﻿USE [EventScavenger]
GO
/****** Object:  StoredProcedure [dbo].[SelectMachineLogListAll]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectMachineLogListAll]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectMachineLogListAll]
GO
/****** Object:  StoredProcedure [dbo].[InsertEventLogEntries]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertEventLogEntries]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertEventLogEntries]
GO
/****** Object:  StoredProcedure [dbo].[GetMachineLogTotalStats]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetMachineLogTotalStats]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetMachineLogTotalStats]
GO
/****** Object:  StoredProcedure [dbo].[GetStatsDailyErrWarnPerMinute]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStatsDailyErrWarnPerMinute]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetStatsDailyErrWarnPerMinute]
GO
/****** Object:  StoredProcedure [dbo].[GetStatsMachineHourlyTotalEWLive]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStatsMachineHourlyTotalEWLive]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetStatsMachineHourlyTotalEWLive]
GO
/****** Object:  StoredProcedure [dbo].[GetStatsMachineHourlyTotalLive]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStatsMachineHourlyTotalLive]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetStatsMachineHourlyTotalLive]
GO
/****** Object:  StoredProcedure [dbo].[GetMachineLogMaxTimeGenerated]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetMachineLogMaxTimeGenerated]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetMachineLogMaxTimeGenerated]
GO
/****** Object:  StoredProcedure [dbo].[GetStatsSourceHourlyTotalLive]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStatsSourceHourlyTotalLive]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetStatsSourceHourlyTotalLive]
GO
/****** Object:  StoredProcedure [dbo].[GetTimeBreakdownByMessage]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetTimeBreakdownByMessage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetTimeBreakdownByMessage]
GO
/****** Object:  StoredProcedure [dbo].[GetStatsSourceEventIDHourlyTotalLive]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStatsSourceEventIDHourlyTotalLive]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetStatsSourceEventIDHourlyTotalLive]
GO
/****** Object:  StoredProcedure [dbo].[InsertEventLogHistory]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertEventLogHistory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertEventLogHistory]
GO
/****** Object:  StoredProcedure [dbo].[CreateEventLogHistory]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CreateEventLogHistory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CreateEventLogHistory]
GO
/****** Object:  StoredProcedure [dbo].[DoCreateHistory]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DoCreateHistory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DoCreateHistory]
GO
/****** Object:  StoredProcedure [dbo].[DoMaintenance]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DoMaintenance]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DoMaintenance]
GO
/****** Object:  StoredProcedure [dbo].[DoReArchiveEntries4Day]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DoReArchiveEntries4Day]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DoReArchiveEntries4Day]
GO
/****** Object:  StoredProcedure [dbo].[SelectEventLogEntries]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectEventLogEntries]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectEventLogEntries]
GO
/****** Object:  StoredProcedure [dbo].[SelectEventLogEntries2]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectEventLogEntries2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectEventLogEntries2]
GO
/****** Object:  StoredProcedure [dbo].[SelectEventLogEntriesByMachineLogName]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectEventLogEntriesByMachineLogName]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectEventLogEntriesByMachineLogName]
GO
/****** Object:  StoredProcedure [dbo].[UpdateEventLogHistory]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateEventLogHistory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdateEventLogHistory]
GO
/****** Object:  StoredProcedure [dbo].[UpdateGlobalSettings]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateGlobalSettings]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdateGlobalSettings]
GO
/****** Object:  StoredProcedure [dbo].[UpdateImportLogs]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateImportLogs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdateImportLogs]
GO
/****** Object:  StoredProcedure [dbo].[SelectMachineLogEntryStats]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectMachineLogEntryStats]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectMachineLogEntryStats]
GO
/****** Object:  StoredProcedure [dbo].[SelectMachineLogList]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectMachineLogList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectMachineLogList]
GO
/****** Object:  StoredProcedure [dbo].[SelectMachineLogList2]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectMachineLogList2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectMachineLogList2]
GO
/****** Object:  StoredProcedure [dbo].[SelectImportLogs]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectImportLogs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectImportLogs]
GO
/****** Object:  StoredProcedure [dbo].[SelectImportLogsByCollector]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectImportLogsByCollector]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectImportLogsByCollector]
GO
/****** Object:  StoredProcedure [dbo].[InsertEventLogEntry]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertEventLogEntry]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertEventLogEntry]
GO
/****** Object:  StoredProcedure [dbo].[DoMaintenanceGlobal]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DoMaintenanceGlobal]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DoMaintenanceGlobal]
GO
/****** Object:  StoredProcedure [dbo].[DoMaintenanceGlobalOld]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DoMaintenanceGlobalOld]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DoMaintenanceGlobalOld]
GO
/****** Object:  StoredProcedure [dbo].[DoMaintenanceMachineLog]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DoMaintenanceMachineLog]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DoMaintenanceMachineLog]
GO
/****** Object:  StoredProcedure [dbo].[DeleteImportLogs]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteImportLogs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DeleteImportLogs]
GO
/****** Object:  StoredProcedure [dbo].[DeleteMachineLog]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteMachineLog]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DeleteMachineLog]
GO
/****** Object:  StoredProcedure [dbo].[InsertImportLogs]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertImportLogs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertImportLogs]
GO
/****** Object:  StoredProcedure [dbo].[SelectArchiveReportByYearMonthDay]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectArchiveReportByYearMonthDay]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectArchiveReportByYearMonthDay]
GO
/****** Object:  StoredProcedure [dbo].[DoArchiveEntriesByDates]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DoArchiveEntriesByDates]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DoArchiveEntriesByDates]
GO
/****** Object:  UserDefinedFunction [dbo].[GetMachineSourcesPerDay]    Script Date: 07/08/2010 14:34:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetMachineSourcesPerDay]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetMachineSourcesPerDay]
GO
/****** Object:  StoredProcedure [dbo].[GetStatsSourceEventIDHourlyTotal]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStatsSourceEventIDHourlyTotal]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetStatsSourceEventIDHourlyTotal]
GO
/****** Object:  StoredProcedure [dbo].[GetStatsMachineHourlyTotal]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStatsMachineHourlyTotal]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetStatsMachineHourlyTotal]
GO
/****** Object:  StoredProcedure [dbo].[GetStatsMachineHourlyTotalEW]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStatsMachineHourlyTotalEW]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetStatsMachineHourlyTotalEW]
GO
/****** Object:  StoredProcedure [dbo].[DeleteCollector]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteCollector]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DeleteCollector]
GO
/****** Object:  StoredProcedure [dbo].[GetStatsSourceHourlyTotal]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStatsSourceHourlyTotal]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetStatsSourceHourlyTotal]
GO
/****** Object:  StoredProcedure [dbo].[InsertMachineLog]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertMachineLog]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertMachineLog]
GO
/****** Object:  StoredProcedure [dbo].[InsertPollFilter]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertPollFilter]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertPollFilter]
GO
/****** Object:  StoredProcedure [dbo].[DeletePollFilter]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeletePollFilter]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DeletePollFilter]
GO
/****** Object:  StoredProcedure [dbo].[SelectMachineLogEntry]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectMachineLogEntry]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectMachineLogEntry]
GO
/****** Object:  StoredProcedure [dbo].[SelectPollFilterEventIDsByPollFilterID]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectPollFilterEventIDsByPollFilterID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectPollFilterEventIDsByPollFilterID]
GO
/****** Object:  StoredProcedure [dbo].[SelectPollFilterSourcesByPollFilterID]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectPollFilterSourcesByPollFilterID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectPollFilterSourcesByPollFilterID]
GO
/****** Object:  StoredProcedure [dbo].[SelectPollingFilters]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectPollingFilters]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectPollingFilters]
GO
/****** Object:  StoredProcedure [dbo].[SelectMachineLogList3]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectMachineLogList3]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectMachineLogList3]
GO
/****** Object:  StoredProcedure [dbo].[SelectMachineLogListByCollector]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectMachineLogListByCollector]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectMachineLogListByCollector]
GO
/****** Object:  StoredProcedure [dbo].[UpdateMachineLogEntry]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateMachineLogEntry]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdateMachineLogEntry]
GO
/****** Object:  StoredProcedure [dbo].[UpdatePollFilter]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdatePollFilter]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdatePollFilter]
GO
/****** Object:  StoredProcedure [dbo].[SetCreateHistorySettings]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetCreateHistorySettings]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SetCreateHistorySettings]
GO
/****** Object:  StoredProcedure [dbo].[SetLastPollTimeForMachineLog]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetLastPollTimeForMachineLog]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SetLastPollTimeForMachineLog]
GO
/****** Object:  StoredProcedure [dbo].[SetMaintenanceLastRunTime]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetMaintenanceLastRunTime]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SetMaintenanceLastRunTime]
GO
/****** Object:  StoredProcedure [dbo].[SetMaintenanceSettings]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetMaintenanceSettings]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SetMaintenanceSettings]
GO
/****** Object:  StoredProcedure [dbo].[SetNextCreateHistoryTime]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetNextCreateHistoryTime]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SetNextCreateHistoryTime]
GO
/****** Object:  StoredProcedure [dbo].[SetNextRecycleTime]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetNextRecycleTime]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SetNextRecycleTime]
GO
/****** Object:  StoredProcedure [dbo].[SetRecyclingCheckFrequencyMinutes]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetRecyclingCheckFrequencyMinutes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SetRecyclingCheckFrequencyMinutes]
GO
/****** Object:  StoredProcedure [dbo].[SetRepeatTimeSec]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetRepeatTimeSec]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SetRepeatTimeSec]
GO
/****** Object:  StoredProcedure [dbo].[SetSettingValue]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetSettingValue]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SetSettingValue]
GO
/****** Object:  StoredProcedure [dbo].[SelectPollFilter]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectPollFilter]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectPollFilter]
GO
/****** Object:  StoredProcedure [dbo].[SelectGlobalSettings]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectGlobalSettings]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectGlobalSettings]
GO
/****** Object:  StoredProcedure [dbo].[SelectSourceList]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectSourceList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectSourceList]
GO
/****** Object:  StoredProcedure [dbo].[SelectSourceList2]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectSourceList2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectSourceList2]
GO
/****** Object:  StoredProcedure [dbo].[SetCollectorNextRecycleTime]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetCollectorNextRecycleTime]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SetCollectorNextRecycleTime]
GO
/****** Object:  StoredProcedure [dbo].[UpdateCollector]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateCollector]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdateCollector]
GO
/****** Object:  StoredProcedure [dbo].[InsertSource]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertSource]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertSource]
GO
/****** Object:  StoredProcedure [dbo].[InsertUserName]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertUserName]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertUserName]
GO
/****** Object:  StoredProcedure [dbo].[InsertCategory]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertCategory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertCategory]
GO
/****** Object:  StoredProcedure [dbo].[InsertCollector]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertCollector]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertCollector]
GO
/****** Object:  StoredProcedure [dbo].[InsertEventID]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertEventID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertEventID]
GO
/****** Object:  StoredProcedure [dbo].[SelectCollectors]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectCollectors]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectCollectors]
GO
/****** Object:  StoredProcedure [dbo].[SelectEventIDList]    Script Date: 07/08/2010 14:34:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectEventIDList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SelectEventIDList]
GO
/****** Object:  StoredProcedure [dbo].[GetCollectorByName]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCollectorByName]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCollectorByName]
GO
/****** Object:  StoredProcedure [dbo].[GetCollectorNextRecycleTime]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCollectorNextRecycleTime]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCollectorNextRecycleTime]
GO
/****** Object:  StoredProcedure [dbo].[GetCollectorThreadRecyclingEnabled]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCollectorThreadRecyclingEnabled]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCollectorThreadRecyclingEnabled]
GO
/****** Object:  StoredProcedure [dbo].[GetEventLogLogMonthlyCountsPivot]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetEventLogLogMonthlyCountsPivot]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetEventLogLogMonthlyCountsPivot]
GO
/****** Object:  StoredProcedure [dbo].[GetRunCreateHistoryEnabled]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetRunCreateHistoryEnabled]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetRunCreateHistoryEnabled]
GO
/****** Object:  StoredProcedure [dbo].[GetSettingValue]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetSettingValue]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetSettingValue]
GO
/****** Object:  StoredProcedure [dbo].[GetEventlogRowCount]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetEventlogRowCount]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetEventlogRowCount]
GO
/****** Object:  StoredProcedure [dbo].[crosstab]    Script Date: 07/08/2010 14:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[crosstab]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[crosstab]
GO
/****** Object:  UserDefinedFunction [dbo].[ufn_CSVToTable]    Script Date: 07/08/2010 14:34:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufn_CSVToTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufn_CSVToTable]
GO
/****** Object:  UserDefinedFunction [dbo].[ufn_CSVToTableInt]    Script Date: 07/08/2010 14:34:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufn_CSVToTableInt]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufn_CSVToTableInt]
GO
/****** Object:  UserDefinedFunction [dbo].[ufn_CSVToTableInt]    Script Date: 07/08/2010 14:34:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufn_CSVToTableInt]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[ufn_CSVToTableInt] ( @StringInput VARCHAR(8000) )
RETURNS @OutputTable TABLE ( [value] int )
AS
BEGIN

	DECLARE @String    VARCHAR(1000)

	IF (not @StringInput is null)
	BEGIN
		WHILE LEN(@StringInput) > 0
		BEGIN
			SET @String      = LEFT(@StringInput, 
									ISNULL(NULLIF(CHARINDEX('','', @StringInput) - 1, -1),
									LEN(@StringInput)))
			SET @StringInput = SUBSTRING(@StringInput,
										 ISNULL(NULLIF(CHARINDEX('','', @StringInput), 0),
										 LEN(@StringInput)) + 1, LEN(@StringInput))
			IF (left(@String, 1) = '''''''')
				set @String = SUBSTRING(@String, 2, len(@String) - 1)
			IF (right(@String, 1) = '''''''')
				set @String = SUBSTRING(@String, 1, len(@String) - 1)
			IF (ISNUMERIC(@String) = 1)
				INSERT INTO @OutputTable ( [value] )
				VALUES ( @String )
		END
	END
	
	RETURN
END

' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[ufn_CSVToTable]    Script Date: 07/08/2010 14:34:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufn_CSVToTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[ufn_CSVToTable] ( @StringInput VARCHAR(8000) )
RETURNS @OutputTable TABLE ( [String] VARCHAR(1000) )
AS
BEGIN

	DECLARE @String    VARCHAR(1000)

	IF (not @StringInput is null)
	BEGIN
		WHILE LEN(@StringInput) > 0
		BEGIN
			SET @String      = LEFT(@StringInput, 
									ISNULL(NULLIF(CHARINDEX('','', @StringInput) - 1, -1),
									LEN(@StringInput)))
			SET @StringInput = SUBSTRING(@StringInput,
										 ISNULL(NULLIF(CHARINDEX('','', @StringInput), 0),
										 LEN(@StringInput)) + 1, LEN(@StringInput))

			IF (left(@String, 1) = '''''''')
				set @String = SUBSTRING(@String, 2, len(@String) - 1)
			IF (right(@String, 1) = '''''''')
				set @String = SUBSTRING(@String, 1, len(@String) - 1)
				
			INSERT INTO @OutputTable ( [String] )
			VALUES ( @String )
		END
	END
	
	RETURN
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[crosstab]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[crosstab]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[crosstab] 
@select varchar(8000),
@sumfunc varchar(100), 
@pivot varchar(100), 
@table varchar(100) 
AS
--from http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC (''SELECT '' + @pivot + '' AS [pivot] INTO ##pivot FROM '' + @table + '' WHERE 1=2'')
EXEC (''INSERT INTO ##pivot SELECT DISTINCT '' + @pivot + '' FROM '' + @table + '' WHERE '' 
+ @pivot + '' Is Not Null'')

SELECT @sql='''',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, '' END)'' )

SELECT @delim=CASE Sign( CharIndex(''char'', data_type)+ CharIndex(''date'', data_type) ) 
WHEN 0 THEN '''' ELSE '''''''' END 
FROM tempdb.information_schema.columns 
WHERE table_name = ''##pivot'' AND column_name = ''pivot''

SELECT @sql = @sql + '''''''' + convert(varchar(100), [pivot]) + '''''' = isnull('' + 
stuff(@sumfunc,charindex( ''('', @sumfunc )+1, 0, '' CASE '' + @pivot + '' WHEN '' 
+ @delim + convert(varchar(100), [pivot]) + @delim + '' THEN '' ) + '','''''''') , '' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql = left(@sql, len(@sql)-1)
SELECT @select = stuff(@select, charindex('' FROM '', @select)+1, 0, '', '' + @sql + '' '')

--SELECT @select

EXEC (@select)
SET ANSI_WARNINGS ON' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetEventlogRowCount]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetEventlogRowCount]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[GetEventlogRowCount]
as
  SELECT 
		i.[rows] as [Rows] 
  FROM 
		sysobjects s inner join sysindexes i 
			on s.[id] = i.[id]
  WHERE 
		s.[Name] like ''EventLogEntries'' 
		and s.type = ''U'' 
		and i.indid in (0, 1, 255)
' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetSettingValue]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetSettingValue]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE proc [dbo].[GetSettingValue]
  @SettingName varchar(50)
as
  SELECT top 1 SettingValue
  FROM Settings
  WHERE SettingName like @SettingName

' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetRunCreateHistoryEnabled]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetRunCreateHistoryEnabled]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[GetRunCreateHistoryEnabled]
as
  Select SettingValue
  From Settings
  where SettingName = ''RunCreateHistoryEnabled''
' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetEventLogLogMonthlyCountsPivot]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetEventLogLogMonthlyCountsPivot]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[GetEventLogLogMonthlyCountsPivot]
as 
exec crosstab 
	''select MachineLog from EventLogLogMonthlyCounts group by MachineLog'',
	''sum(Records)'',
	''[YearMonth]'',
	''EventLogLogMonthlyCounts''' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetCollectorThreadRecyclingEnabled]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCollectorThreadRecyclingEnabled]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[GetCollectorThreadRecyclingEnabled]
(
  @CollectorName varchar(100)
)
as
  SELECT 
	ThreadRecyclingEnabled
  FROM 
	Collectors
  WHERE 
	Name = @CollectorName
' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetCollectorNextRecycleTime]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCollectorNextRecycleTime]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[GetCollectorNextRecycleTime]
(
  @CollectorName varchar(100)
)
as
  SELECT 
	NextRecycleTime
  FROM 
	Collectors
  WHERE 
	Name = @CollectorName
' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetCollectorByName]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCollectorByName]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[GetCollectorByName]
(
	@CollectorName varchar(100)
)
as
  SELECT 
		CollectorID,
		[Name],
		ThreadRecycleFrequencyMinutes,
		NextRecycleTime,
		ThreadRecyclingEnabled,
		ImportsEnabled,
		ImportFrequencyMinutes
  FROM 
	dbo.Collectors
  WHERE
	[Name] like @CollectorName
' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectEventIDList]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectEventIDList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[SelectEventIDList]
AS
  SELECT 
	EventID 
  FROM 
	EventIDs
/*
  FROM 
	EventLog 
  GROUP BY 
	EventID 
*/
  ORDER BY 
	EventID

' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectCollectors]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectCollectors]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE Proc [dbo].[SelectCollectors]
as
  Select 
	CollectorID,
	[Name],
	ThreadRecycleFrequencyMinutes,
	NextRecycleTime,
	ThreadRecyclingEnabled,
	ImportsEnabled,
	ImportFrequencyMinutes
  From
	Collectors
  Order by
	[Name]' 
END
GO
/****** Object:  StoredProcedure [dbo].[InsertEventID]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertEventID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[InsertEventID]
	@EventID int
as
  IF not exists(SELECT EventID FROM EventIDs WHERE EventID = @EventID)
  begin
	INSERT EventIDs(EventID)
	VALUES(@EventID)
  end' 
END
GO
/****** Object:  StoredProcedure [dbo].[InsertCollector]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertCollector]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[InsertCollector]
 (
	@Name varchar(100),
	@ThreadRecycleFrequencyMinutes int,
	@NextRecycleTime datetime,
	@ThreadRecyclingEnabled bit,
	@ImportsEnabled bit = 0,
	@ImportFrequencyMinutes int = 15
 )
 as
   If (not exists(select * from Collectors where [Name] like @Name))
   begin
	 Insert Collectors
	 (
		Name, 
		ThreadRecycleFrequencyMinutes, 
		NextRecycleTime, 
		ThreadRecyclingEnabled,
		ImportsEnabled,
		ImportFrequencyMinutes
	 )
	 Values 
	 (
		@Name, 
		@ThreadRecycleFrequencyMinutes, 
		@NextRecycleTime, 
		@ThreadRecyclingEnabled,
		@ImportsEnabled,
		@ImportFrequencyMinutes
	 )
   end
' 
END
GO
/****** Object:  StoredProcedure [dbo].[InsertCategory]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertCategory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[InsertCategory]
(
	@Category varchar(50)
)
as
  IF not exists(SELECT CategoryID FROM EventCategories WHERE Category = @Category)
  begin
   INSERT EventCategories (Category)
   SELECT @Category
  end' 
END
GO
/****** Object:  StoredProcedure [dbo].[InsertUserName]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertUserName]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'Create proc [dbo].[InsertUserName]
(
	@UserName varchar(50)
)
as
  IF not exists(SELECT * FROM UserNames WHERE UserName = @UserName )
  begin
   INSERT UserNames (UserName)
   SELECT @UserName
  end' 
END
GO
/****** Object:  StoredProcedure [dbo].[InsertSource]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertSource]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[InsertSource]
(
	@Source varchar(255)
)
as
  IF not exists(SELECT SourceID FROM EventSources WHERE Source = @Source)
  begin
   INSERT EventSources (Source)
   SELECT @Source
  end' 
END
GO
/****** Object:  StoredProcedure [dbo].[UpdateCollector]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateCollector]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' CREATE proc [dbo].[UpdateCollector]
 (
	@CollectorID int,
	@Name varchar(100),
	@ThreadRecycleFrequencyMinutes int,
	@NextRecycleTime datetime,
	@ThreadRecyclingEnabled bit,
	@ImportsEnabled bit = 0,
	@ImportFrequencyMinutes int = 15
 )
 as
   Update 
	 Collectors
   Set Name = @Name,
		ThreadRecycleFrequencyMinutes = @ThreadRecycleFrequencyMinutes,
		NextRecycleTime = @NextRecycleTime,
		ThreadRecyclingEnabled = @ThreadRecyclingEnabled,
		ImportsEnabled = @ImportsEnabled,
		ImportFrequencyMinutes = @ImportFrequencyMinutes
   Where
	 CollectorID = @CollectorID' 
END
GO
/****** Object:  StoredProcedure [dbo].[SetCollectorNextRecycleTime]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetCollectorNextRecycleTime]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[SetCollectorNextRecycleTime] 
(
  @CollectorName varchar(100)
)
as
  DECLARE @NextRecycleTime datetime
  DECLARE @ThreadRecycleFrequencyMinutes int
  SELECT 
	@ThreadRecycleFrequencyMinutes = ThreadRecycleFrequencyMinutes,
	@NextRecycleTime = NextRecycleTime
  FROM 
	Collectors
  WHERE 
	Name = @CollectorName
	
  IF (@NextRecycleTime is null)
	SET @NextRecycleTime = ''2010-1-1''
  while (@NextRecycleTime < getdate())
  begin
	  set @NextRecycleTime = dateadd(n, @ThreadRecycleFrequencyMinutes, @NextRecycleTime)
  end
  
  UPDATE Collectors
  SET NextRecycleTime = @NextRecycleTime
  WHERE 
	Name = @CollectorName
' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectSourceList2]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectSourceList2]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'Create Proc [dbo].[SelectSourceList2]
AS
  SELECT 
	SourceID,
	Source
  FROM 
	EventSources 
  ORDER BY 
	Source' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectSourceList]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectSourceList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE Proc [dbo].[SelectSourceList]
AS
  SELECT 
	Source as SourceName 
  FROM 
	EventSources 
  GROUP BY 
	Source
  ORDER BY 
	Source
' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectGlobalSettings]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectGlobalSettings]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[SelectGlobalSettings]
as
	declare @MaxLogEntries int 
	declare @MaintenanceEnabled bit
	declare @MaintenanceFrequencyMinutes int
	declare @MaintenanceRunner varchar(50)
	declare @MaintenanceLastRun varchar(50)
	declare @MaintenanceTimeOutSeconds int
	declare @RecyclingCheckFrequencyMinutes int
	declare @NextCreateHistoryTime datetime
	declare @RunCreateHistoryEnabled bit
	declare @NextCreateHistoryFrequencyMinutes int
	declare @DaysToHistory int
	declare @RepeatTimeSec int

	select @MaxLogEntries = cast(SettingValue as int) from Settings where SettingName like ''MaxLogEntries''
	select @MaintenanceEnabled = cast(SettingValue as bit) from Settings where SettingName like ''MaintenanceEnabled''
	select @MaintenanceFrequencyMinutes = cast(SettingValue as int) from Settings where SettingName like ''MaintenanceFrequencyMinutes''
	select @MaintenanceRunner = SettingValue from Settings where SettingName like ''MaintenanceRunner''
	select @MaintenanceLastRun = SettingValue from Settings where SettingName like ''MaintenanceLastRun''
	select @MaintenanceTimeOutSeconds = cast(SettingValue as int) from Settings where SettingName like ''MaintenanceTimeOutSeconds''
	select @RecyclingCheckFrequencyMinutes = cast(SettingValue as int) from Settings where SettingName like ''RecyclingCheckFrequencyMinutes''
	select @NextCreateHistoryTime = convert(datetime, SettingValue) from Settings where SettingName like ''NextCreateHistoryTime''
	select @RunCreateHistoryEnabled = cast(SettingValue as bit) from Settings where SettingName like ''RunCreateHistoryEnabled''
	select @NextCreateHistoryFrequencyMinutes = cast(SettingValue as int) from Settings where SettingName like ''NextCreateHistoryFrequencyMinutes''
	select @DaysToHistory = cast(SettingValue as int) from Settings where SettingName like ''DaysToHistory''
	select @RepeatTimeSec = cast(SettingValue as int) from Settings where SettingName like ''RepeatTimeSec''

	select 
		@MaxLogEntries as [MaxLogEntries],
		@MaintenanceEnabled as [MaintenanceEnabled],
		@MaintenanceFrequencyMinutes as [MaintenanceFrequencyMinutes],
		@MaintenanceRunner as [MaintenanceRunner],
		@MaintenanceLastRun as [MaintenanceLastRun],
		@MaintenanceTimeOutSeconds as [MaintenanceTimeOutSeconds],
		@RecyclingCheckFrequencyMinutes as [RecyclingCheckFrequencyMinutes],
		@NextCreateHistoryTime as [NextCreateHistoryTime],
		@RunCreateHistoryEnabled as [RunCreateHistoryEnabled],
		@NextCreateHistoryFrequencyMinutes as [NextCreateHistoryFrequencyMinutes],
		@DaysToHistory as [DaysToHistory],
		@RepeatTimeSec as [RepeatTimeSec]
' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectPollFilter]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectPollFilter]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[SelectPollFilter]
(
	@PollFilterID int
)
as
  select 
	[Name],
	[MessageContains],
	[LastChanged],
	ExcludeInfo,
	ExcludeWarn,
	ExcludeErr,
	ExcludeSucAud,
	ExcludeFailAud
  from
	dbo.PollFilters
  where
	PollFilterID = @PollFilterID
' 
END
GO
/****** Object:  StoredProcedure [dbo].[SetSettingValue]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetSettingValue]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE proc [dbo].[SetSettingValue]
  @SettingName varchar(50),
  @SettingValue varchar(50)
as
  IF Exists(SELECT * from Settings WHERE SettingName like @SettingName)
	UPDATE Settings
	SET SettingValue = @SettingValue
	WHERE SettingName like @SettingName
  ELSE
	INSERT Settings(SettingName, SettingValue)
	VALUES (@SettingName, @SettingValue)

' 
END
GO
/****** Object:  StoredProcedure [dbo].[SetRepeatTimeSec]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetRepeatTimeSec]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[SetRepeatTimeSec]
(
	@RepeatTimeSec int
)
as
  UPDATE dbo.Settings
  Set SettingValue = cast(@RepeatTimeSec as varchar(50))
  WHERE SettingName = ''RepeatTimeSec''
' 
END
GO
/****** Object:  StoredProcedure [dbo].[SetRecyclingCheckFrequencyMinutes]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetRecyclingCheckFrequencyMinutes]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[SetRecyclingCheckFrequencyMinutes]
(
	@RecyclingCheckFrequencyMinutes int
)
as
  UPDATE dbo.Settings
  Set SettingValue = cast(@RecyclingCheckFrequencyMinutes as varchar(50))
  WHERE SettingName = ''RecyclingCheckFrequencyMinutes''
' 
END
GO
/****** Object:  StoredProcedure [dbo].[SetNextRecycleTime]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetNextRecycleTime]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create proc [dbo].[SetNextRecycleTime]
as
  DECLARE @NextRecycleFrequencyMinutesStr varchar(50)
  DECLARE @NextRecycleFrequencyMinutes int
  DECLARE @NextRecycleTimeStr varchar(50)
  DECLARE @NextRecycleTime datetime

  SELECT @NextRecycleFrequencyMinutesStr = SettingValue
  FROM Settings
  WHERE SettingName = ''NextRecycleFrequencyMinutes''

  if (isnumeric(@NextRecycleFrequencyMinutesStr) = 0)
	raiserror (''Invalid number specified for NextRecycleFrequencyMinutes'', 16, 1)
  else
  begin
	set @NextRecycleFrequencyMinutes = convert(int, @NextRecycleFrequencyMinutesStr)
	SELECT @NextRecycleTimeStr = SettingValue
	FROM Settings
	WHERE SettingName = ''NextRecycleTime''
	
	if (ISDATE(@NextRecycleTimeStr) = 1)
	begin
	  set @NextRecycleTime = convert(datetime, @NextRecycleTimeStr)
	  if (@NextRecycleFrequencyMinutes > 0)
	  begin
		while (@NextRecycleTime < getdate())
		begin
		  set @NextRecycleTime = dateadd(n, @NextRecycleFrequencyMinutes, @NextRecycleTime)
		end
		UPDATE Settings 
		SET SettingValue = convert(varchar(50), @NextRecycleTime, 120)
		WHERE SettingName = ''NextRecycleTime''
	  end
	 end 
	else
	  raiserror (''Invalid date specified for NextRecycleTime'', 16, 1)
  end  
  
  

/*
SELECT  SettingValue
	FROM Settings
	WHERE SettingName = ''NextRecycleTime''
*/

' 
END
GO
/****** Object:  StoredProcedure [dbo].[SetNextCreateHistoryTime]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetNextCreateHistoryTime]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
Create proc [dbo].[SetNextCreateHistoryTime]
as
  DECLARE @NextCreateHistoryFrequencyMinutesStr varchar(50)
  DECLARE @NextCreateHistoryFrequencyMinutes int
  DECLARE @NextCreateHistoryTimeStr varchar(50)
  DECLARE @NextCreateHistoryTime datetime

  SELECT @NextCreateHistoryFrequencyMinutesStr = SettingValue
  FROM Settings
  WHERE SettingName = ''NextCreateHistoryFrequencyMinutes''

  if (isnumeric(@NextCreateHistoryFrequencyMinutesStr) = 0)
	raiserror (''Invalid number specified for NextCreateHistoryFrequencyMinutes'', 16, 1)
  else
  begin
	set @NextCreateHistoryFrequencyMinutes = convert(int, @NextCreateHistoryFrequencyMinutesStr)
	SELECT @NextCreateHistoryTimeStr = SettingValue
	FROM Settings
	WHERE SettingName = ''NextCreateHistoryTime''
	
	if (ISDATE(@NextCreateHistoryTimeStr) = 1)
	begin
	  set @NextCreateHistoryTime = convert(datetime, @NextCreateHistoryTimeStr)
	  if (@NextCreateHistoryFrequencyMinutes > 0)
	  begin
		while (@NextCreateHistoryTime < getdate())
		begin
		  set @NextCreateHistoryTime = dateadd(n, @NextCreateHistoryFrequencyMinutes, @NextCreateHistoryTime)
		end
		UPDATE Settings 
		SET SettingValue = convert(varchar(50), @NextCreateHistoryTime, 120)
		WHERE SettingName = ''NextCreateHistoryTime''
	  end
	 end 
	else
	  raiserror (''Invalid date specified for NextCreateHistoryTime'', 16, 1)
  end ' 
END
GO
/****** Object:  StoredProcedure [dbo].[SetMaintenanceSettings]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetMaintenanceSettings]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[SetMaintenanceSettings]
(
	@MaintenanceEnabled bit,
	@MaintenanceFrequencyMinutes int,
	@MaintenanceRunner varchar(50)
)
as
  If (@MaintenanceEnabled = 0)
	UPDATE dbo.Settings
	Set SettingValue = ''False''
	WHERE SettingName = ''MaintenanceEnabled''
  Else
	UPDATE dbo.Settings
	Set SettingValue = ''True''
	WHERE SettingName = ''MaintenanceEnabled''
	
  UPDATE dbo.Settings
  Set SettingValue = cast(@MaintenanceFrequencyMinutes as varchar(50))
  WHERE SettingName = ''MaintenanceFrequencyMinutes''
  
  UPDATE dbo.Settings
  Set SettingValue = @MaintenanceRunner
  WHERE SettingName = ''MaintenanceRunner''
' 
END
GO
/****** Object:  StoredProcedure [dbo].[SetMaintenanceLastRunTime]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetMaintenanceLastRunTime]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'Create proc [dbo].[SetMaintenanceLastRunTime]
as
  DECLARE @MaintenanceLastRun datetime
  Set @MaintenanceLastRun = GETDATE()
  UPDATE Settings 
  SET SettingValue = convert(varchar(50), @MaintenanceLastRun, 120)
  WHERE SettingName = ''MaintenanceLastRun'' 
' 
END
GO
/****** Object:  StoredProcedure [dbo].[SetLastPollTimeForMachineLog]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetLastPollTimeForMachineLog]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE proc [dbo].[SetLastPollTimeForMachineLog]
(
  @Machine varchar(255),
  @LogName varchar(255),
  @LastPollTime datetime 
)
as 
  UPDATE 
	dbo.MachineLogs
  SET 
	LastPollTime = @LastPollTime
  WHERE 
	Machine = @Machine AND
	LogName = @LogName

' 
END
GO
/****** Object:  StoredProcedure [dbo].[SetCreateHistorySettings]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SetCreateHistorySettings]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[SetCreateHistorySettings]
(
	@Enabled bit,
	@NextCreateHistoryTime datetime,
	@NextCreateHistoryFrequencyMinutes int,
	@DaysToHistory int
)
as
  Declare @EnabledStr varchar(50)
  Declare @NextCreateHistoryTimeStr varchar(50)
  Declare @NextCreateHistoryFrequencyMinutesStr varchar(50)
  Declare @DaysToHistoryStr varchar(50)
  
  If (@Enabled = 0)
	Set @EnabledStr = ''False''
  else
	Set @EnabledStr = ''True''
  exec SetSettingValue ''RunCreateHistoryEnabled'', @EnabledStr

  Set @NextCreateHistoryTimeStr = convert(varchar(50), @NextCreateHistoryTime, 120)
  exec SetSettingValue ''NextCreateHistoryTime'', @NextCreateHistoryTimeStr
  
  Set @NextCreateHistoryFrequencyMinutesStr = cast(@NextCreateHistoryFrequencyMinutes as varchar(50))
  exec SetSettingValue ''NextCreateHistoryFrequencyMinutes'', @NextCreateHistoryFrequencyMinutesStr
  
  Set @DaysToHistoryStr = cast(@DaysToHistory as varchar(50))
  exec SetSettingValue ''DaysToHistory'', @DaysToHistoryStr
' 
END
GO
/****** Object:  StoredProcedure [dbo].[UpdatePollFilter]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdatePollFilter]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[UpdatePollFilter]
(
	@PollFilterID int,
	@Name nvarchar(100),
	@MessageContains nvarchar(1024),
	--@LastChanged,
	@ExcludeInfo bit,
	@ExcludeWarn bit,
	@ExcludeErr bit,
	@ExcludeSucAud bit,
	@ExcludeFailAud bit,
	@PollFilterEventIDIncludes varchar(2048) = '''',
	@PollFilterEventIDExcludes varchar(2048) = '''',
	@PollFilterSourceIncludes varchar(2048) = '''',
	@PollFilterSourceExcludes varchar(2048) = ''''
)
as
  Update dbo.PollFilters
  Set 
	[Name] = @Name ,
	MessageContains = @MessageContains,
	LastChanged = GETDATE(),
	ExcludeInfo = @ExcludeInfo,
	ExcludeWarn = @ExcludeWarn,
	ExcludeErr = @ExcludeErr,
	ExcludeSucAud = @ExcludeSucAud,
	ExcludeFailAud = @ExcludeFailAud
  Where
	PollFilterID = @PollFilterID
	
  --Clear all existing entries for PollFilterID
  delete dbo.PollFilterEventIDs
  Where
	PollFilterID = @PollFilterID
  
  insert dbo.PollFilterEventIDs(PollFilterID, EventID, Exclude)
  select 
	@PollFilterID,
	[value],
	0
  from
	dbo.ufn_CSVToTableInt(@PollFilterEventIDIncludes)
  insert dbo.PollFilterEventIDs(PollFilterID, EventID, Exclude)
  select 
	@PollFilterID,
	[value],
	1
  from
	dbo.ufn_CSVToTableInt(@PollFilterEventIDExcludes)
  
  --Clear all existing entries for PollFilterID
  delete dbo.PollFilterSources
  Where
	PollFilterID = @PollFilterID
	
  insert dbo.PollFilterSources(PollFilterID, SourceID, Exclude)
  select 
	@PollFilterID,
	[value],
	0
  from
	dbo.ufn_CSVToTableInt(@PollFilterSourceIncludes)
  insert dbo.PollFilterSources(PollFilterID, SourceID, Exclude)
  select 
	@PollFilterID,
	[value],
	1
  from
	dbo.ufn_CSVToTableInt(@PollFilterSourceExcludes)' 
END
GO
/****** Object:  StoredProcedure [dbo].[UpdateMachineLogEntry]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateMachineLogEntry]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE Proc [dbo].[UpdateMachineLogEntry]
(
	@CollectorID int,
	@Machine varchar(255),
	@LogName varchar(255),
	@LastPollTime datetime,
	@Enabled bit,
	@PollFrequency int,
	@MaxEntryCount int,
	@PollFilterID int = null
)
AS
  IF not exists(
	SELECT * 
	FROM MachineLogs 
	WHERE 
		Machine = @Machine AND
		LogName = @LogName)
  begin
		INSERT MachineLogs(CollectorID, Machine, LogName, LastPollTime, [Enabled], PollFrequency, MaxEntryCount, PollFilterID)
		VALUES (@CollectorID, @Machine, @LogName, @LastPollTime, @Enabled, @PollFrequency, @MaxEntryCount, @PollFilterID)
  end
  else
  begin
		  UPDATE
			MachineLogs
		  SET 
			CollectorID = @CollectorID,
			LastPollTime = @LastPollTime,
			[Enabled] = @Enabled,
			PollFrequency = @PollFrequency,
			MaxEntryCount = @MaxEntryCount,
			PollFilterID = @PollFilterID
		  WHERE 
			Machine = @Machine AND
			LogName = @LogName
  end' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectMachineLogListByCollector]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectMachineLogListByCollector]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[SelectMachineLogListByCollector]
(
	@Collector varchar(100)
)
as
select 
	m.MachineLogID,
	c.CollectorID,
	c.Name as [Collector],
	Machine,
	LogName,
	[Enabled],
	LastPollTime,
	PollFrequency,
	MaxEntryCount
from 
	(MachineLogs m inner join Collectors c 
	  on m.CollectorID = c.CollectorID) 
where 
	c.Name like @Collector
order by 
	Machine,
	LogName
	' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectMachineLogList3]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectMachineLogList3]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE  proc [dbo].[SelectMachineLogList3]
(
	@showDisabled bit = 0
)
as
	 SELECT 
		m.MachineLogID,
		m.Machine,
		m.LogName,
		m.LastPollTime,
		m.Enabled,
		m.PollFrequency
	FROM 
		MachineLogs m
	WHERE 
		(@showDisabled = 1) or
		(m.Enabled = 1)
	ORDER BY
		m.Machine,
		m.LogName
' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectPollingFilters]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectPollingFilters]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[SelectPollingFilters]
as
  select 
	PollFilterID,
	[Name] ,
	MessageContains,
	LastChanged,
	ExcludeInfo,
	ExcludeWarn,
	ExcludeErr,
	ExcludeSucAud,
	ExcludeFailAud,
	(select COUNT(*) from dbo.PollFilterEventIDs e where p.PollFilterID = e.PollFilterID) as EventIDFilterCount,
	(select COUNT(*) from dbo.PollFilterSources s where p.PollFilterID = s.PollFilterID) as SourceFilterCount
  from
	dbo.PollFilters p
  order by 
	[Name]
' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectPollFilterSourcesByPollFilterID]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectPollFilterSourcesByPollFilterID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[SelectPollFilterSourcesByPollFilterID]
(
		@PollFilterID int
)
as
	select 
		s.SourceID,
		s.Source,
		p.Exclude
	from 
		dbo.EventSources s inner join dbo.PollFilterSources p
			on s.SourceID = p.SourceID
	where
		p.PollFilterID = @PollFilterID
	order by
		s.Source' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectPollFilterEventIDsByPollFilterID]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectPollFilterEventIDsByPollFilterID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'	create proc [dbo].[SelectPollFilterEventIDsByPollFilterID]
	(
		@PollFilterID int
	)
	as
	select 
		EventID,
		Exclude
	from
		dbo.PollFilterEventIDs
	where
		PollFilterID = @PollFilterID' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectMachineLogEntry]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectMachineLogEntry]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE  proc [dbo].[SelectMachineLogEntry]
  @Machine varchar(255),
  @LogName varchar(255)
as
  SELECT 
	m.MachineLogID,
	c.CollectorID,
	c.[Name] as [Collector],
	m.Machine,
	m.LogName,
	m.LastPollTime,
	m.[Enabled],
	m.PollFrequency,
	m.MaxEntryCount,
	m.PollFilterID,
	p.Name as [PollingFilterName],
	p.LastChanged as [PollFilterLastChanged]
  FROM 
	(dbo.MachineLogs m left join dbo.PollFilters p 
		on m.PollFilterID = p.PollFilterID)
		Inner join dbo.Collectors c 
		on m.CollectorID = c.CollectorID
  WHERE 
	Machine = @Machine AND
	LogName = @LogName' 
END
GO
/****** Object:  StoredProcedure [dbo].[DeletePollFilter]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeletePollFilter]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[DeletePollFilter]
(
	@PollFilterID int
)
as
	if not exists(select * from dbo.MachineLogs where PollFilterID = @PollFilterID)
	begin
		delete dbo.PollFilterEventIDs
		Where
			PollFilterID = @PollFilterID
		delete dbo.PollFilterSources
		Where
			PollFilterID = @PollFilterID
		delete dbo.PollFilters
		Where
			PollFilterID = @PollFilterID
	end
' 
END
GO
/****** Object:  StoredProcedure [dbo].[InsertPollFilter]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertPollFilter]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[InsertPollFilter]
(
	@Name nvarchar(100),
	@MessageContains nvarchar(1024),
	--@LastChanged,
	@ExcludeInfo bit,
	@ExcludeWarn bit,
	@ExcludeErr bit,
	@ExcludeSucAud bit,
	@ExcludeFailAud bit,
	@PollFilterEventIDIncludes varchar(2048) = '''',
	@PollFilterEventIDExcludes varchar(2048) = '''',
	@PollFilterSourceIncludes varchar(2048) = '''',
	@PollFilterSourceExcludes varchar(2048) = ''''
)
as
  Insert dbo.PollFilters([Name], MessageContains, LastChanged, 
	ExcludeInfo, ExcludeWarn, ExcludeErr, ExcludeSucAud , ExcludeFailAud )
  values (
		@Name ,
		@MessageContains,
		GETDATE(),
		@ExcludeInfo,
		@ExcludeWarn,
		@ExcludeErr,
		@ExcludeSucAud,
		@ExcludeFailAud
  )
  Declare @PollFilterID int
  select @PollFilterID = IDENT_CURRENT(''PollFilters'')
	
  insert dbo.PollFilterEventIDs(PollFilterID, EventID, Exclude)
  select 
	@PollFilterID,
	[value],
	0
  from
	dbo.ufn_CSVToTableInt(@PollFilterEventIDIncludes)
  insert dbo.PollFilterEventIDs(PollFilterID, EventID, Exclude)
  select 
	@PollFilterID,
	[value],
	1
  from
	dbo.ufn_CSVToTableInt(@PollFilterEventIDExcludes)

  insert dbo.PollFilterSources(PollFilterID, SourceID, Exclude)
  select 
	@PollFilterID,
	[value],
	0
  from
	dbo.ufn_CSVToTableInt(@PollFilterSourceIncludes)
  insert dbo.PollFilterSources(PollFilterID, SourceID, Exclude)
  select 
	@PollFilterID,
	[value],
	1
  from
	dbo.ufn_CSVToTableInt(@PollFilterSourceExcludes)
' 
END
GO
/****** Object:  StoredProcedure [dbo].[InsertMachineLog]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertMachineLog]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[InsertMachineLog]
(
	@Machine varchar(255),
	@LogName varchar(255)
)
as
  DECLARE @CollectorID int
  
  select @CollectorID = CollectorID 
  from Collectors
  where Name like 
	(select top 1 SettingValue from dbo.Settings where SettingName = ''MaintenanceRunner'')
	
  IF (@CollectorID is null)
	select top 1 @CollectorID = CollectorID from Collectors
	
  IF not exists(SELECT * FROM MachineLogs WHERE Machine = @Machine AND LogName = @LogName )
  begin
   INSERT MachineLogs (CollectorID, Machine, LogName, [Enabled], PollFrequency)
   SELECT @CollectorID, @Machine, @LogName, 0, 15
  end
  ' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetStatsSourceHourlyTotal]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStatsSourceHourlyTotal]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE proc [dbo].[GetStatsSourceHourlyTotal]
(
  @date datetime
)
AS
  SELECT 
	m.Machine + ''\'' + LogName as [Log],
	s.Source,
	sum(case [Hour]
		when 0 then Records else 0 end ) as [0],
	sum(case [Hour]
		when 1 then Records else 0 end ) as [1],
	sum(case [Hour]
		when 2 then Records else 0 end ) as [2],
	sum(case [Hour]
		when 3 then Records else 0 end ) as [3],
	sum(case [Hour]
		when 4 then Records else 0 end ) as [4],
	sum(case [Hour]
		when 5 then Records else 0 end ) as [5],	
	sum(case [Hour]
		when 6 then Records else 0 end ) as [6],
	sum(case [Hour]
		when 7 then Records else 0 end ) as [7],
	sum(case [Hour]
		when 8 then Records else 0 end ) as [8],
	sum(case [Hour]
		when 9 then Records else 0 end ) as [9],
	sum(case [Hour]
		when 10 then Records else 0 end ) as [10],
	sum(case [Hour]
		when 11 then Records else 0 end ) as [11],	
	sum(case [Hour]
		when 12 then Records else 0 end ) as [12],
	sum(case [Hour]
		when 13 then Records else 0 end ) as [13],
	sum(case [Hour]
		when 14 then Records else 0 end ) as [14],
	sum(case [Hour]
		when 15 then Records else 0 end ) as [15],
	sum(case [Hour]
		when 16 then Records else 0 end ) as [16],
	sum(case [Hour]
		when 17 then Records else 0 end ) as [17],	
	sum(case [Hour]
		when 18 then Records else 0 end ) as [18],
	sum(case [Hour]
		when 19 then Records else 0 end ) as [19],
	sum(case [Hour]
		when 20 then Records else 0 end ) as [20],
	sum(case [Hour]
		when 21 then Records else 0 end ) as [21],
	sum(case [Hour]
		when 22 then Records else 0 end ) as [22],
	sum(case [Hour]
		when 23 then Records else 0 end ) as [23],
	sum(Records) as [Total]
  FROM MachineLogs m inner join EventLogHistory h 
	on m.MachineLogID = h.MachineLogID
	inner join EventSources s 
		on h.SourceID = s.SourceID
  WHERE 
	Year(@date) = [Year] and
	Month(@date) = [Month] and
	Day(@date) = [Day]
  GROUP BY
	m.Machine + ''\'' + m.LogName,
	s.Source
  ORDER BY
	m.Machine + ''\'' + m.LogName,
	s.Source

' 
END
GO
/****** Object:  StoredProcedure [dbo].[DeleteCollector]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteCollector]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'Create proc [dbo].[DeleteCollector]
 (
	@CollectorID int
 )
as
   If (not exists(select * from MachineLogs where CollectorID = @CollectorID))
   begin
	 Delete Collectors 
	 Where CollectorID = @CollectorID
   end
' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetStatsMachineHourlyTotalEW]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStatsMachineHourlyTotalEW]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE proc [dbo].[GetStatsMachineHourlyTotalEW]
(
  @date datetime
)
AS
  SELECT 
	m.Machine + ''\'' + LogName as [Log],
	EventTypeName as [Type],
	sum(case [Hour]
		when 0 then Records else 0 end ) as [0],
	sum(case [Hour]
		when 1 then Records else 0 end ) as [1],
	sum(case [Hour]
		when 2 then Records else 0 end ) as [2],
	sum(case [Hour]
		when 3 then Records else 0 end ) as [3],
	sum(case [Hour]
		when 4 then Records else 0 end ) as [4],
	sum(case [Hour]
		when 5 then Records else 0 end ) as [5],
	sum(case [Hour]
		when 6 then Records else 0 end ) as [6],
	sum(case [Hour]
		when 7 then Records else 0 end ) as [7],
	sum(case [Hour]
		when 8 then Records else 0 end ) as [8],
	sum(case [Hour]
		when 9 then Records else 0 end ) as [9],
	sum(case [Hour]
		when 10 then Records else 0 end ) as [10],
	sum(case [Hour]
		when 11 then Records else 0 end ) as [11],
	sum(case [Hour]
		when 12 then Records else 0 end ) as [12],
	sum(case [Hour]
		when 13 then Records else 0 end ) as [13],
	sum(case [Hour]
		when 14 then Records else 0 end ) as [14],
	sum(case [Hour]
		when 15 then Records else 0 end ) as [15],
	sum(case [Hour]
		when 16 then Records else 0 end ) as [16],
	sum(case [Hour]
		when 17 then Records else 0 end ) as [17],
	sum(case [Hour]
		when 18 then Records else 0 end ) as [18],
	sum(case [Hour]
		when 19 then Records else 0 end ) as [19],
	sum(case [Hour]
		when 20 then Records else 0 end ) as [20],
	sum(case [Hour]
		when 21 then Records else 0 end ) as [21],
	sum(case [Hour]
		when 22 then Records else 0 end ) as [22],
	sum(case [Hour]
		when 23 then Records else 0 end ) as [23],
	sum(Records) as [Total]
  FROM 
	MachineLogs m inner join EventLogHistory h 
	on m.MachineLogID = h.MachineLogID
  WHERE 
	Year(@date) = [Year] and
	Month(@date) = [Month] and
	Day(@date) = [Day]
  GROUP BY
	m.Machine + ''\'' + LogName,
	EventTypeName
  ORDER BY
	m.Machine + ''\'' + LogName,
	EventTypeName

' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetStatsMachineHourlyTotal]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStatsMachineHourlyTotal]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE proc [dbo].[GetStatsMachineHourlyTotal]
(
  @date datetime
)
AS
  SELECT 
	m.Machine + ''\'' + LogName as [Log],
	sum(case [Hour]
		when 0 then Records else 0 end ) as [0],
	sum(case [Hour]
		when 1 then Records else 0 end ) as [1],
	sum(case [Hour]
		when 2 then Records else 0 end ) as [2],
	sum(case [Hour]
		when 3 then Records else 0 end ) as [3],
	sum(case [Hour]
		when 4 then Records else 0 end ) as [4],
	sum(case [Hour]
		when 5 then Records else 0 end ) as [5],
	sum(case [Hour]
		when 6 then Records else 0 end ) as [6],
	sum(case [Hour]
		when 7 then Records else 0 end ) as [7],
	sum(case [Hour]
		when 8 then Records else 0 end ) as [8],
	sum(case [Hour]
		when 9 then Records else 0 end ) as [9],
	sum(case [Hour]
		when 10 then Records else 0 end ) as [10],
	sum(case [Hour]
		when 11 then Records else 0 end ) as [11],
	sum(case [Hour]
		when 12 then Records else 0 end ) as [12],
	sum(case [Hour]
		when 13 then Records else 0 end ) as [13],
	sum(case [Hour]
		when 14 then Records else 0 end ) as [14],
	sum(case [Hour]
		when 15 then Records else 0 end ) as [15],
	sum(case [Hour]
		when 16 then Records else 0 end ) as [16],
	sum(case [Hour]
		when 17 then Records else 0 end ) as [17],
	sum(case [Hour]
		when 18 then Records else 0 end ) as [18],
	sum(case [Hour]
		when 19 then Records else 0 end ) as [19],
	sum(case [Hour]
		when 20 then Records else 0 end ) as [20],
	sum(case [Hour]
		when 21 then Records else 0 end ) as [21],
	sum(case [Hour]
		when 22 then Records else 0 end ) as [22],
	sum(case [Hour]
		when 23 then Records else 0 end ) as [23],
	sum(Records) as [Total]
  FROM 
	MachineLogs m inner join EventLogHistory h 
	on m.MachineLogID = h.MachineLogID
  WHERE 
	Year(@date) = [Year] and
	Month(@date) = [Month] and
	Day(@date) = [Day]
  GROUP BY
	m.Machine + ''\'' + LogName
  ORDER BY
	m.Machine + ''\'' + LogName

' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetStatsSourceEventIDHourlyTotal]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStatsSourceEventIDHourlyTotal]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE proc [dbo].[GetStatsSourceEventIDHourlyTotal]
(
  @date datetime
)
AS
  SELECT 
	m.Machine + ''\'' + LogName as [Log],
	EventTypeName as [Type],
	EventID,
	sum(case [Hour]
		when 0 then Records else 0 end ) as [0],
	sum(case [Hour]
		when 1 then Records else 0 end ) as [1],
	sum(case [Hour]
		when 2 then Records else 0 end ) as [2],
	sum(case [Hour]
		when 3 then Records else 0 end ) as [3],
	sum(case [Hour]
		when 4 then Records else 0 end ) as [4],
	sum(case [Hour]
		when 5 then Records else 0 end ) as [5],
	sum(case [Hour]
		when 6 then Records else 0 end ) as [6],
	sum(case [Hour]
		when 7 then Records else 0 end ) as [7],
	sum(case [Hour]
		when 8 then Records else 0 end ) as [8],
	sum(case [Hour]
		when 9 then Records else 0 end ) as [9],
	sum(case [Hour]
		when 10 then Records else 0 end ) as [10],
	sum(case [Hour]
		when 11 then Records else 0 end ) as [11],
	sum(case [Hour]
		when 12 then Records else 0 end ) as [12],
	sum(case [Hour]
		when 13 then Records else 0 end ) as [13],
	sum(case [Hour]
		when 14 then Records else 0 end ) as [14],
	sum(case [Hour]
		when 15 then Records else 0 end ) as [15],
	sum(case [Hour]
		when 16 then Records else 0 end ) as [16],
	sum(case [Hour]
		when 17 then Records else 0 end ) as [17],
	sum(case [Hour]
		when 18 then Records else 0 end ) as [18],
	sum(case [Hour]
		when 19 then Records else 0 end ) as [19],
	sum(case [Hour]
		when 20 then Records else 0 end ) as [20],
	sum(case [Hour]
		when 21 then Records else 0 end ) as [21],
	sum(case [Hour]
		when 22 then Records else 0 end ) as [22],
	sum(case [Hour]
		when 23 then Records else 0 end ) as [23],
	sum(Records) as [Total]
  FROM 
	MachineLogs m inner join EventLogHistory h 
	on m.MachineLogID = h.MachineLogID
  WHERE 
	Year(@date) = [Year] and
	Month(@date) = [Month] and
	Day(@date) = [Day]
  GROUP BY
	m.Machine + ''\'' + LogName,
	EventTypeName,
	EventID
  ORDER BY
	m.Machine + ''\'' + LogName,
	EventTypeName,
	EventID

' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[GetMachineSourcesPerDay]    Script Date: 07/08/2010 14:34:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetMachineSourcesPerDay]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'create function [dbo].[GetMachineSourcesPerDay]
(
	@MachineLogID int,
	@fromDate datetime,
	@toDate datetime
)
returns varchar(8000)
as
begin
  declare @totalSources varchar(8000)
  declare @source varchar(255)
  set @totalSources = ''''
  
  DECLARE loopSource_cursor   CURSOR  FORWARD_ONLY
  FOR 
	select Distinct [Source] 
	from 
		dbo.EventLogEntries e inner join dbo.EventSources s on
			e.SourceID = s.SourceID
	where
		MachineLogID = @MachineLogID and
		TimeGenerated between @fromDate and @toDate
	order by [Source]
	
   OPEN loopSource_cursor

   FETCH NEXT FROM loopSource_cursor INTO @source

   WHILE @@FETCH_STATUS = 0
   BEGIN
	 if (datalength(@totalSources) = 0)
	   set @totalSources = @source
	 else
	   set @totalSources = @totalSources + '', '' + @source
	 
	 FETCH NEXT FROM loopSource_cursor INTO @source
   END
   CLOSE loopSource_cursor
   DEALLOCATE loopSource_cursor
   
   RETURN @totalSources
end
' 
END
GO
/****** Object:  StoredProcedure [dbo].[DoArchiveEntriesByDates]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DoArchiveEntriesByDates]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[DoArchiveEntriesByDates]
(
	@FromDate datetime,
	@ToDate datetime
)
as
	DECLARE @ArchiveCount int
	DECLARE @MachineLogID int
	DECLARE @EventID int
	DECLARE @Year int
	DECLARE @Month smallint
	DECLARE @Day smallint
	DECLARE @Hour smallint
	DECLARE @SourceID int
	DECLARE @EventTypeName char(1)
	DECLARE @Archived bit
	DECLARE archive_cursor CURSOR 
	 FORWARD_ONLY
	 FOR 
	   SELECT
		MachineLogID, 
		EventID, 
		year(TimeGenerated), 
		month(TimeGenerated), 
		day(TimeGenerated), 
		datepart(HOUR,TimeGenerated),
		SourceID,
		EventTypeName,
		Archived
	  FROM 
		dbo.EventLogEntries
	  WHERE 
		TimeGenerated between @FromDate and @ToDate 
		and Archived = 0
	  ORDER BY 
		TimeGenerated
		
	OPEN archive_cursor
	FETCH NEXT FROM archive_cursor 
	INTO @MachineLogID, @EventID, @Year, @Month, @Day, @Hour, @SourceID, @EventTypeName, @Archived

	SET nocount on
	SET @ArchiveCount = 0

	WHILE @@FETCH_STATUS = 0
	BEGIN
	  --If the history table does have an instance then increment records
	  IF exists(select * from EventLogHistory h 
			where 
				@MachineLogID = h.MachineLogID AND
				@EventID = h.EventID AND
				@Year = h.[Year] AND
				@Month = h.[Month] AND
				@Day = h.[Day] AND
				@Hour = h.[Hour] AND
				@SourceID = h.SourceID AND
				@EventTypeName = h.EventTypeName
				)
	  BEGIN
		UPDATE EventLogHistory
		SET Records = Records + 1
		WHERE
				@MachineLogID = MachineLogID AND
				@EventID = EventID AND
				@Year = [Year] AND
				@Month = [Month] AND
				@Day = [Day] AND
				@Hour = [Hour] AND
				@SourceID = SourceID AND
				@EventTypeName = EventTypeName
	  END
	  ELSE
	  BEGIN
		INSERT EventLogHistory(
			MachineLogID, 
			EventID, 
			[Year], 
			[Month], 
			[Day], 
			[Hour], 
			SourceID,
			EventTypeName, 
			Records)
		 SELECT
			@MachineLogID, 
			@EventID,
			@Year,
			@Month,
			@Day,
			@Hour,
			@SourceID,
			@EventTypeName,
			1
	  END

	  UPDATE dbo.EventLogEntries
	  SET [Archived] = 1
	  WHERE CURRENT OF archive_cursor

	  SET @ArchiveCount = @ArchiveCount + 1
	  FETCH NEXT FROM archive_cursor 
	  INTO @MachineLogID, @EventID, @Year, @Month, @Day, @Hour, @SourceID, @EventTypeName, @Archived
	END

	CLOSE archive_cursor
	DEALLOCATE archive_cursor
	
	SET nocount off
	select @FromDate as [From], @ToDate as [To], @ArchiveCount as [RecordsArchived]
' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectArchiveReportByYearMonthDay]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectArchiveReportByYearMonthDay]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[SelectArchiveReportByYearMonthDay]
(
	@Year int,
	@Month int,
	@Day int = 0
)
as
  select 
	h.[Year],
	h.[Month],
	h.[Day],
	SUM(h.Records) as [History],
	(select COUNT(*) 
	 from EventLogEntries e  with (readpast)
	 where 
		year(TimeGenerated) = h.[Year] and 
		month(TimeGenerated) = h.[Month] and 
		day(TimeGenerated) = h.[Day] and
		e.Archived = 1) as [Archived Entries],
	(select COUNT(*) 
	 from EventLogEntries e  with (readpast)
	 where 
		year(TimeGenerated) = h.[Year] and 
		month(TimeGenerated) = h.[Month] and 
		day(TimeGenerated) = h.[Day] and
		e.Archived = 0) as [Not Archived Entries]
  from 
	EventLogHistory h with (readpast)
  where 
	h.[Year] = @Year and
	h.[Month] = @Month and 
	(@Day = 0 or h.[Day] = @Day)
  group by
	h.[Year],
	h.[Month],
	h.[Day]
  order by
	h.[Year],
	h.[Month],
	h.[Day]
' 
END
GO
/****** Object:  StoredProcedure [dbo].[InsertImportLogs]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertImportLogs]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[InsertImportLogs]
(
	@CollectorID int,
	@Server nvarchar(255),
	@Database nvarchar(255),
	@MachineLogID int,
	@Enabled bit
)
AS
  INSERT dbo.ImportLogs(CollectorID, [Server], [Database], MachineLogID, [Enabled])
  VALUES (@CollectorID, @Server, @Database, @MachineLogID, @Enabled)
' 
END
GO
/****** Object:  StoredProcedure [dbo].[DeleteMachineLog]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteMachineLog]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[DeleteMachineLog]
(
	@MachineLogID int
)
as
begin
  --remove all history items
  delete EventLogHistory
  where 
	MachineLogID = @MachineLogID
  --remove EventLogEntries records
  delete EventLogEntries
  where 
	MachineLogID = @MachineLogID
  --delete MachineLogs record
  delete MachineLogs
  where 
	MachineLogID = @MachineLogID
end
' 
END
GO
/****** Object:  StoredProcedure [dbo].[DeleteImportLogs]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteImportLogs]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create proc [dbo].[DeleteImportLogs]
(
	@ForeignLogID int
)
AS
  DELETE dbo.ImportLogs
  WHERE
	ForeignLogID = @ForeignLogID
' 
END
GO
/****** Object:  StoredProcedure [dbo].[DoMaintenanceMachineLog]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DoMaintenanceMachineLog]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[DoMaintenanceMachineLog]
as
	Set nocount on

	declare @MachineLogID int
	declare @MaxEntryCount int
	declare @EntryCount int
	declare @TimeGeneratedEarliest datetime
	declare @BufferSize int
	declare @busy bit
	set @BufferSize = 5000
	
	declare MachineLogCursor cursor 
	FOR SELECT MachineLogID, MaxEntryCount FROM MachineLogs with (readpast)
	OPEN MachineLogCursor
	FETCH NEXT FROM MachineLogCursor INTO @MachineLogID, @MaxEntryCount
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
	  set @busy = 0
	  
	  WHILE @busy = 0
	  BEGIN
		  --Get current entry count
		  select 
				@EntryCount = count(*) 
		  from 
				EventLogEntries
		  where
				MachineLogID = @MachineLogID
		  
		  --If current count > max allowed count
		  If (@EntryCount > @MaxEntryCount)
		  BEGIN
			set ROWCOUNT @MaxEntryCount
			select @TimeGeneratedEarliest = TimeGenerated 
			from EventLogEntries with (readpast)
			where MachineLogID = @MachineLogID
			order by TimeGenerated desc
			
			select @MachineLogID, @TimeGeneratedEarliest, @EntryCount
			--if affected count > 0
			if ((select count(*) 
				from EventLogEntries with (readpast)
				where MachineLogID = @MachineLogID
					and TimeGenerated < @TimeGeneratedEarliest) > 0)
			BEGIN
				delete top (@BufferSize) EventLogEntries 
				where MachineLogID = @MachineLogID
					and TimeGenerated < @TimeGeneratedEarliest
			END
			ELSE
			  set @busy = 1
		  END
		  ELSE
			Set @busy = 1
	  END
	  FETCH NEXT FROM MachineLogCursor INTO @MachineLogID, @MaxEntryCount
	END
	CLOSE MachineLogCursor
	DEALLOCATE MachineLogCursor
' 
END
GO
/****** Object:  StoredProcedure [dbo].[DoMaintenanceGlobalOld]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DoMaintenanceGlobalOld]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[DoMaintenanceGlobalOld]
as
  set nocount on
  declare @MaxLogEntries varchar(10)
  declare @TimeGeneratedEarliest datetime
  SELECT @MaxLogEntries = SettingValue
  FROM Settings
  WHERE SettingName like ''MaxLogEntries''
  if (@MaxLogEntries is null)
  begin
	insert Settings(SettingName, SettingValue) values (''MaxLogEntries'', ''500000'')
	set @MaxLogEntries = ''500000''
  end
  
  CREATE table #EarliestTimes(TimeGenerated datetime)
  EXEC (''insert #EarliestTimes(TimeGenerated) select top 1 TimeGenerated '' +
		''from EventLogEntries with (readpast) where TimeGenerated in '' +
		   ''(select top '' + @MaxLogEntries + '' TimeGenerated from EventLogEntries with (readpast) order by TimeGenerated desc) '' +
		''order by TimeGenerated asc'')
  
  --EXEC (''insert #EarliestTimes(TimeGenerated) select top '' + @MaxLogEntries + '' TimeGenerated from EventLogEntries order by TimeGenerated desc'')
  SELECT top 1 @TimeGeneratedEarliest = TimeGenerated from #EarliestTimes --order by TimeGenerated asc
  DROP table #EarliestTimes
  SET nocount off
  DELETE EventLogEntries WHERE TimeGenerated < @TimeGeneratedEarliest' 
END
GO
/****** Object:  StoredProcedure [dbo].[DoMaintenanceGlobal]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DoMaintenanceGlobal]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[DoMaintenanceGlobal]
as
--pseudo code
--1. get current table size and max allowed number
--2. if table size > max allowed + some buffer size (e.g. 5000) else end
--3. select newest entry date of top 5000 sorted by entry date asc
--4. go to step 2

declare @MaxLogEntries int
declare @EntryCount int
declare @BufferSize int
declare @TimeGeneratedEarliest datetime
declare @EntryCountStr varchar(50)

set @BufferSize = 5000

SELECT @MaxLogEntries = cast(SettingValue as int)
FROM Settings
WHERE SettingName like ''MaxLogEntries''

select @EntryCount = count(*)
from dbo.EventLogEntries with (readpast)
select @EntryCountStr = convert(varchar(50),@EntryCount )

select @EntryCountStr as [Current entry count], @MaxLogEntries as [Max allowed]

while ( @EntryCount + @BufferSize > @MaxLogEntries)
begin
  print ''Trimming oldest 5000 > current '' + @EntryCountStr
  
  CREATE table #EarliestTimesX(TimeGenerated datetime)

  insert #EarliestTimesX(TimeGenerated)
  select top 5000 TimeGenerated 
  from EventLogEntries with (readpast)
  order by TimeGenerated asc

  SELECT top 1 @TimeGeneratedEarliest = TimeGenerated from #EarliestTimesX order by TimeGenerated desc
  DROP table #EarliestTimesX
  
  DELETE EventLogEntries WHERE TimeGenerated < @TimeGeneratedEarliest
  
  select @EntryCount = count(*)
  from dbo.EventLogEntries with (readpast)
  select @EntryCountStr = convert(varchar(50),@EntryCount )
end 
print ''Done''
' 
END
GO
/****** Object:  StoredProcedure [dbo].[InsertEventLogEntry]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertEventLogEntry]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[InsertEventLogEntry]
(
	@ComputerName varchar(255),
	@EventLog varchar(255),
	@RecordNumber int,
	@EventID int,
	@TimeGenerated datetime,
	@SourceName varchar(255),
	@EventTypeName varchar(255),
	@Strings nvarchar(2048),
	@Message nvarchar(4000),
	@Category varchar(50) = null,
	@UserName varchar(50) = null
)
as
  DECLARE @MachineLogID int
  DECLARE @SourceID int
  DECLARE @CategoryID int
  DECLARE @FormattedMessage nvarchar(4000)
  DECLARE @UserNameID int
  DECLARE @RepeatTimeSec int
  declare @duplicates int
  declare @mustInsert bit
  declare @lastErrorMsg varchar(max)
  
  set @mustInsert = 1

	SET NOCOUNT ON
	SELECT @RepeatTimeSec = cast(SettingValue as int)
	FROM Settings
	WHERE SettingName like ''RepeatTimeSec''
	
	exec InsertMachineLog @ComputerName, @EventLog
	exec InsertSource @SourceName
	exec InsertCategory @Category
	exec InsertEventID @EventID
	SELECT @MachineLogID = MachineLogID FROM MachineLogs WHERE Machine = @ComputerName and LogName = @EventLog
	SELECT @SourceID = SourceID FROM EventSources WHERE Source = @SourceName 
	IF (@Category is null)
		set @CategoryID = 1
	ELSE
		SELECT @CategoryID = CategoryID
		FROM EventCategories
		WHERE @Category = Category
	IF @UserName is null
	   set @UserNameID = null
	ELSE
	   SELECT @UserNameID = UserNameID FROM UserNames WHERE UserName = @UserName
		
	IF ((@Strings != null) and (datalength(@Strings) > 0))
	  Set @FormattedMessage = Left(@Message + char(13) + char(10) + @Strings, 4000)
	ELSE
	  Set @FormattedMessage = Left(@Message, 4000)
	  
	SET NOCOUNT OFF	
	--exact duplicates are not allowed
	IF (exists(select * from EventLogEntries with (READCOMMITTED)
			where TimeGenerated = @TimeGenerated and
			MachineLogID = @MachineLogID and
			RecordNumber = @RecordNumber)
		)
	BEGIN
	   Print ''Duplicate record''
	   set @lastErrorMsg = ''Duplicate record''
	   set @mustInsert = 0
	END
	ELSE
	BEGIN
		declare @RepeatTimeFrom datetime, @RepeatTimeTo datetime
		set @RepeatTimeFrom = dateadd(SECOND, -@RepeatTimeSec, @TimeGenerated)
		set @RepeatTimeTo = dateadd(SECOND, @RepeatTimeSec, @TimeGenerated)
		--first check if there are any recent entries for this event log
		IF (exists(select * from EventLogEntries with (READCOMMITTED)
				where 
					TimeGenerated between @RepeatTimeFrom and @RepeatTimeTo
					and MachineLogID = @MachineLogID
					and not RecordNumber = @RecordNumber
					)
		 )
		BEGIN
			   --check for recent similar entry that match all values
			IF (exists(select * from EventLogEntries with (READCOMMITTED)
				where 
					TimeGenerated between @RepeatTimeFrom and @RepeatTimeTo
					and MachineLogID = @MachineLogID
					and not RecordNumber = @RecordNumber				
					and EventID = @EventID
					and SourceID = @SourceID
					and EventTypeName = @EventTypeName
					and [Message] = @FormattedMessage -- message must match as well!
					)
			 )
			 BEGIN
				UPDATE EventLogEntries with (READCOMMITTED)
				SET [Repeat] = [Repeat] + 1
				WHERE 
					TimeGenerated between @RepeatTimeFrom and @RepeatTimeTo
						and MachineLogID = @MachineLogID
						and not RecordNumber = @RecordNumber				
						and EventID = @EventID
						and SourceID = @SourceID
						and EventTypeName = @EventTypeName
						and [Message] = @FormattedMessage -- message must match as well!
				set @mustInsert = 0
			 END
		END
		IF (@mustInsert = 1) --it wasn''t a repeat record
		BEGIN
			BEGIN TRY
				INSERT EventLogEntries with (READCOMMITTED)
				(	
					TimeGenerated,
					RecordNumber,
					MachineLogID,
					EventID,
					SourceID,
					EventTypeName,
					[Message],
					CategoryID,
					UserNameID
				  )
				  VALUES(
					@TimeGenerated,
					@RecordNumber,
					@MachineLogID,
					@EventID,
					@SourceID,
					Left(@EventTypeName, 1),
					@FormattedMessage,
					@CategoryID,
					@UserNameID
				  )
			END TRY
			BEGIN CATCH
				Print ''Insert failure''
				set @lastErrorMsg = ERROR_MESSAGE()			
			END CATCH	
		END
	END	

	SELECT @mustInsert, @lastErrorMsg' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectImportLogsByCollector]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectImportLogsByCollector]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[SelectImportLogsByCollector]
(
	@CollectorID int
)
as
  SELECT 
	i.[Server],
	i.[Database],
	m.Machine,
	m.LogName
  FROM
	dbo.ImportLogs i INNER JOIN dbo.MachineLogs m
	  on i.MachineLogID = m.MachineLogID
  WHERE 
	i.CollectorID = @CollectorID AND
	i.[Enabled] = 1	
  ORDER BY 
	i.[Server],
	i.[Database]' 
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertEventLogEntries]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc InsertEventLogEntries
(
	@xml XML
)
AS
 SET NOCOUNT ON
 declare @StartTime datetime
 declare @EndTime datetime
 set @StartTime = GETDATE()
 
 --Create temp table
 declare @EventEntriesTmp table(
	ComputerName varchar(255),
	EventLog varchar(255),
	RecordNumber int,
	EventID int,
	TimeGenerated datetime,
	SourceName varchar(255),
	EventTypeName char(1),
	[Message] nvarchar(4000),
	CategoryName varchar(50),
	UserName varchar(50),
	MachineLogID int null,
	SourceID int null,
	CategoryID int null,
	UserNameID int null
 )
 --convert/Insert xml data into temp table
 Insert @EventEntriesTmp(ComputerName, EventLog, RecordNumber, EventID, TimeGenerated,
	SourceName, EventTypeName, CategoryName, UserName, [Message])
 SELECT T.Item.value(''@cn'', ''VARCHAR(255)'') ComputerName,
	T.Item.value(''@l'', ''varchar(255)'') EventLog,
	T.Item.value(''@r'', ''Int'') RecordNumber,
	T.Item.value(''@e'', ''Int'') EventID,
	T.Item.value(''@t'', ''DateTime'') TimeGenerated,
	T.Item.value(''@s'', ''varchar(255)'') SourceName,
	Left(T.Item.value(''@et'', ''varchar(255)''),1) EventTypeName,
	T.Item.value(''@c'', ''varchar(50)'') Category,
	T.Item.value(''@u'', ''varchar(50)'') UserName,
	replace(T.Item.value(''@m'', ''varchar(4000)''), ''\r\n'', char(13) + char(10)) [Message]
	FROM   @xml.nodes(''/rs/r'') AS T(Item)

 --setting up the MachineLogID
 Update @EventEntriesTmp
 set MachineLogID = m.MachineLogID 
 FROM @EventEntriesTmp e inner join MachineLogs m with(readpast) on 
	 e.ComputerName = m.Machine and e.EventLog = m.LogName
	
 --Adding any new event sources
 Insert EventSources(Source)
 select e.SourceName
 from @EventEntriesTmp e left join EventSources s with(readpast) on
	 e.SourceName = s.Source
 where s.SourceID is null
 group by e.SourceName
--Setting the SourceID''s
 Update @EventEntriesTmp
 Set SourceID = s.SourceID
 From @EventEntriesTmp e inner join EventSources s with(readpast) on
	 e.SourceName = s.Source
	
--Adding any new Categories in the new row set
 Insert EventCategories(Category)
 select e.CategoryName
 from @EventEntriesTmp e left join EventCategories c with(readpast) on 
	  e.CategoryName = c.Category
 where c.CategoryID is null and len(e.CategoryName) > 0
 group by e.CategoryName
--Setting the CategoryID''s 
 Update @EventEntriesTmp
 set CategoryID = c.CategoryID
 from @EventEntriesTmp e inner join dbo.EventCategories c with(readpast) on
	e.CategoryName = c.Category
 Update @EventEntriesTmp
 set CategoryID = 1
 where CategoryID is null

 --Adding any new EventID''s
 Insert EventIDs(EventID)
 select e.EventID 
 from @EventEntriesTmp e left join EventIDs i with(readpast) on
	e.EventID = i.EventID
 where i.EventID is null
 group by e.EventID

 --Adding any non empty UserNames
 Insert UserNames(UserName)
 select e.UserName
 from @EventEntriesTmp e left join UserNames u with(readpast) on
	 e.UserName = u.UserName
 where u.UserNameID is null and len(e.UserName) > 0
 group by e.UserName
 
 --Setting the UserNameID
 Update @EventEntriesTmp
 set UserNameID = u.UserNameID
 From @EventEntriesTmp e inner join UserNames u with(readpast) on
	e.UserName = u.UserName
 where len(e.UserName) > 0

 --START INSERTING DATA USING A CURSOR
 declare @TimeGenerated datetime
 declare @MachineLogID int
 declare @RecordNumber int
 declare @EventID int
 declare @SourceID int
 declare @EventTypeName char(1)
 declare @Message nvarchar(4000)
 declare @CategoryID int
 declare @UserNameID int 
 declare @RepeatTimeSec int
 declare @inserts int
 declare @duplicates int
 declare @repeats int
 declare @mustInsert bit
 declare @insertFailures int
 declare @lastErrorMsg varchar(max)

 set @inserts = 0
 set @duplicates = 0
 set @repeats = 0
 set @mustInsert = 1
 set @insertFailures = 0

 --Get repeat time setting
 SELECT @RepeatTimeSec = cast(SettingValue as int)
	FROM Settings
	WHERE SettingName like ''RepeatTimeSec''

 DECLARE Events_cursor CURSOR
 FOR Select 
	TimeGenerated, MachineLogID, RecordNumber, 
	EventID, SourceID, EventTypeName, e.[Message], CategoryID, UserNameID
 From 
	@EventEntriesTmp e
 Order by TimeGenerated
 
 OPEN Events_cursor
 FETCH NEXT FROM Events_cursor
 INTO @TimeGenerated, @MachineLogID, @RecordNumber, @EventID, @SourceID, @EventTypeName, @Message, @CategoryID, @UserNameID

 WHILE @@FETCH_STATUS = 0
 BEGIN
	set @mustInsert = 1
	--exact duplicates are not allowed
	IF (exists(select * from EventLogEntries with (READCOMMITTED)
			where TimeGenerated = @TimeGenerated and
			MachineLogID = @MachineLogID and
			RecordNumber = @RecordNumber)
		)
	 BEGIN
	   Print ''Duplicate record''
	   set @duplicates = @duplicates + 1
	 END
	ELSE -- else test if it is a repeat record
	 BEGIN 
		declare @RepeatTimeFrom datetime, @RepeatTimeTo datetime
		set @RepeatTimeFrom = dateadd(SECOND, -@RepeatTimeSec, @TimeGenerated)
		set @RepeatTimeTo = dateadd(SECOND, @RepeatTimeSec, @TimeGenerated)        
		
		--first check if there are any recent entries for this event log
		IF (exists(select * from EventLogEntries with (READCOMMITTED)
				where 
					TimeGenerated between @RepeatTimeFrom and @RepeatTimeTo
					and MachineLogID = @MachineLogID
					and not RecordNumber = @RecordNumber
					)
		 )
		 BEGIN
			   --check for recent similar entry that match all values
			IF (exists(select * from EventLogEntries with (READCOMMITTED)
				where 
					TimeGenerated between @RepeatTimeFrom and @RepeatTimeTo
					and MachineLogID = @MachineLogID
					and not RecordNumber = @RecordNumber				
					and EventID = @EventID
					and SourceID = @SourceID
					and EventTypeName = @EventTypeName
					and [Message] = @Message -- message must match as well!
					)
			 )
			 BEGIN
				UPDATE EventLogEntries with (READCOMMITTED)
				SET [Repeat] = [Repeat] + 1
				WHERE 
					TimeGenerated between @RepeatTimeFrom and @RepeatTimeTo
						and MachineLogID = @MachineLogID
						and not RecordNumber = @RecordNumber				
						and EventID = @EventID
						and SourceID = @SourceID
						and EventTypeName = @EventTypeName
						and [Message] = @Message -- message must match as well!
				set @mustInsert = 0
				set @repeats = @repeats + 1
			 END
		 END
		 IF (@mustInsert = 1) --it wasn''t a repeat record
		 BEGIN
			BEGIN TRY
				INSERT EventLogEntries with (READCOMMITTED)
				(	
					TimeGenerated,
					RecordNumber,
					MachineLogID,
					EventID,
					SourceID,
					EventTypeName,
					[Message],
					CategoryID,
					UserNameID
				  )
				  VALUES(
					@TimeGenerated,
					@RecordNumber,
					@MachineLogID,
					@EventID,
					@SourceID,
					@EventTypeName,
					@Message,
					@CategoryID,
					@UserNameID
				  )
				  set @inserts = @inserts + 1
			END TRY
			BEGIN CATCH
				Print ''Insert failure''
				set @lastErrorMsg = ERROR_MESSAGE()
				set @insertFailures = @insertFailures + 1				
			END CATCH			    
		 END
	 END
	  
	FETCH NEXT FROM Events_cursor
	INTO @TimeGenerated, @MachineLogID, @RecordNumber, 
	@EventID, @SourceID, @EventTypeName, @Message, @CategoryID, @UserNameID
 END
 CLOSE Events_cursor
 DEALLOCATE Events_cursor

 --Updating dbo.MachineLogs latest update times
 DECLARE Latest_cursor CURSOR
 FOR 
  Select 
	MachineLogID, max(TimeGenerated)
  From 
	@EventEntriesTmp
  Group by MachineLogID
 OPEN Latest_cursor
 FETCH NEXT FROM Latest_cursor
 INTO @MachineLogID, @TimeGenerated
 WHILE @@FETCH_STATUS = 0
 BEGIN
  Update MachineLogs
  Set LastPollTime = @TimeGenerated
  Where MachineLogID = @MachineLogID
  
  FETCH NEXT FROM Latest_cursor
  INTO @MachineLogID, @TimeGenerated
 END
 CLOSE Latest_cursor
 DEALLOCATE Latest_cursor

 set @EndTime = GETDATE()
 select @inserts as Inserts, @duplicates as Duplicates, @repeats as Repeats, @insertFailures as InsertFailures,
		@StartTime as StartTime, @EndTime as EndTime, DATEDIFF(millisecond, @StartTime, @EndTime) as MSDuration,
		@lastErrorMsg as LastErrorMsg'
END
GO
/****** Object:  StoredProcedure [dbo].[SelectImportLogs]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectImportLogs]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[SelectImportLogs]
as
  SELECT 
	  i.ForeignLogID,
	  c.CollectorID,
	  c.Name as [CollectorName],
	  [Server],
	  [Database],
	  m.MachineLogID,
	  m.Machine,
	  m.LogName,
	  i.[Enabled]
  FROM
	  dbo.ImportLogs i inner join dbo.Collectors c
		on i.CollectorID = c.CollectorID 
	  inner join dbo.MachineLogs m 
		on i.MachineLogID = m.MachineLogID
  ORDER BY
	  c.Name,
	  [Server],
	  [Database],
	   m.Machine,
	  m.LogName' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectMachineLogList2]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectMachineLogList2]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE proc [dbo].[SelectMachineLogList2]
(
	@hideDisabled bit = 0
)
as
select 
	m.MachineLogID,
	c.CollectorID,
	c.Name as [Collector],
	Machine,
	LogName,
	[Enabled],
	LastPollTime,
	PollFrequency,
	MaxEntryCount,
	min(TimeGenerated) as [Oldest Entry], 
	max(TimeGenerated) as [Newest Entry],
	count(EventID) as [Entry Count],
	m.PollFilterID
from 
	(MachineLogs m inner join Collectors c 
	  on m.CollectorID = c.CollectorID) 
	left join EventLogEntries e on
		m.MachineLogID = e.MachineLogID
where
	@hideDisabled = 0 or
	[Enabled] = 1
group by 
	m.MachineLogID,
	c.CollectorID,
	c.Name,
	Machine,
	LogName,
	[Enabled],
	LastPollTime,
	PollFrequency,
	MaxEntryCount,
	m.PollFilterID
order by 
	c.Name,
	Machine,
	LogName' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectMachineLogList]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectMachineLogList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE  proc [dbo].[SelectMachineLogList]
(
	@showStats bit = 0,
	@showDisabled bit = 0
)
as
  if (@showStats = 0)
  begin
	 SELECT 
		m.MachineLogID,
		m.Machine,
		m.LogName,
		m.LastPollTime,
		m.Enabled,
		m.PollFrequency,
		''N/A'' as [Oldest Entry],
		''N/A''  as [Newest Entry],
		''N/A''  as [Entry Count]
	FROM 
		MachineLogs m
	WHERE 
		(@showDisabled = 1) or
		(m.Enabled = 1)
	ORDER BY
		m.Machine,
		m.LogName
  end    
  else
  begin
	SELECT 
		m.MachineLogID,
		m.Machine,
		m.LogName,
		m.LastPollTime,
		m.Enabled,
		m.PollFrequency,
		(select min(TimeGenerated) from EventLogEntries e where m.MachineLogID = MachineLogID)  as [Oldest Entry], 
		(select max(TimeGenerated) from EventLogEntries e where m.MachineLogID = MachineLogID)  as [Newest Entry],
		(select count(EventID) from EventLogEntries e where m.MachineLogID = MachineLogID)as [Entry Count]
	FROM 
		MachineLogs m
	WHERE 
		(@showDisabled = 1) or
		(m.Enabled = 1)
	ORDER BY
		m.Machine,
		m.LogName
  end
' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectMachineLogEntryStats]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectMachineLogEntryStats]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[SelectMachineLogEntryStats] 
(
	@MachineLogID int
)
as
  select
	(select min(TimeGenerated) from EventLogEntries e where MachineLogID = @MachineLogID)  as [Oldest Entry], 
	(select max(TimeGenerated) from EventLogEntries e where MachineLogID = @MachineLogID)  as [Newest Entry],
	(select count(EventID) from EventLogEntries e where MachineLogID = @MachineLogID)as [Entry Count]
' 
END
GO
/****** Object:  StoredProcedure [dbo].[UpdateImportLogs]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateImportLogs]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[UpdateImportLogs]
(
	@ForeignLogID int,
	@CollectorID int,
	@Server nvarchar(255),
	@Database nvarchar(255),
	@MachineLogID int,
	@Enabled bit
)
AS
  UPDATE dbo.ImportLogs
  SET
	CollectorID = @CollectorID,
	[Server] = @Server, 
	[Database] = @Database, 
	MachineLogID = @MachineLogID,
	[Enabled] = @Enabled
  WHERE
	ForeignLogID = @ForeignLogID' 
END
GO
/****** Object:  StoredProcedure [dbo].[UpdateGlobalSettings]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateGlobalSettings]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[UpdateGlobalSettings]
(
	@MaxLogEntries int,
	@MaintenanceEnabled bit,
	@MaintenanceFrequencyMinutes int,
	@MaintenanceRunner varchar(50),	
	@RecyclingCheckFrequencyMinutes int,	
	@NextCreateHistoryTime datetime,
	@RunCreateHistoryEnabled bit,
	@NextCreateHistoryFrequencyMinutes int,
	@DaysToHistory int,	
	@RepeatTimeSec int,
	@MaintenanceTimeOutSeconds int = 300
)
as
	IF not exists(select * from [dbo].[Settings] WHERE [SettingName] like N''MaxLogEntries'')
		INSERT [dbo].[Settings] ([SettingName], [SettingValue]) 
		VALUES (N''MaxLogEntries'', N''500000'')
	Exec SetSettingValue ''MaxLogEntries'',  @MaxLogEntries
		
	IF not exists(select * from [dbo].[Settings] WHERE [SettingName] like N''MaintenanceEnabled'')
		INSERT [dbo].[Settings] ([SettingName], [SettingValue]) 
		VALUES (N''MaintenanceEnabled'', N''True'')	    
	IF not exists(select * from [dbo].[Settings] WHERE [SettingName] like N''MaintenanceFrequencyMinutes'')
		INSERT [dbo].[Settings] ([SettingName], [SettingValue]) 
		VALUES (N''MaintenanceFrequencyMinutes'', N''15'')
	IF not exists(select * from [dbo].[Settings] WHERE [SettingName] like N''MaintenanceRunner'')
		INSERT [dbo].[Settings] ([SettingName], [SettingValue]) 
		VALUES (N''MaintenanceRunner'', N''(Default collector name)'')
	Exec SetMaintenanceSettings @MaintenanceEnabled, @MaintenanceFrequencyMinutes, @MaintenanceRunner
	IF not exists(select * from [dbo].[Settings] WHERE [SettingName] like N''MaintenanceTimeOutSeconds'')
		INSERT [dbo].[Settings] ([SettingName], [SettingValue]) 
		VALUES (N''MaintenanceTimeOutSeconds'', N''300'')
	Exec SetSettingValue ''MaintenanceTimeOutSeconds'',  @MaintenanceTimeOutSeconds
		
	IF not exists(select * from [dbo].[Settings] WHERE [SettingName] like N''RecyclingCheckFrequencyMinutes'')
		INSERT [dbo].[Settings] ([SettingName], [SettingValue]) 
		VALUES (N''RecyclingCheckFrequencyMinutes'', N''5'')
	Exec SetRecyclingCheckFrequencyMinutes @RecyclingCheckFrequencyMinutes

	IF not exists(select * from [dbo].[Settings] WHERE [SettingName] like N''RunCreateHistoryEnabled'')
		INSERT [dbo].[Settings] ([SettingName], [SettingValue]) 
		VALUES (N''RunCreateHistoryEnabled'', N''True'')
	IF not exists(select * from [dbo].[Settings] WHERE [SettingName] like N''NextCreateHistoryTime'')
		INSERT [dbo].[Settings] ([SettingName], [SettingValue]) 
		VALUES (N''NextCreateHistoryTime'', N''2010-01-01 00:00:00'')			    
	IF not exists(select * from [dbo].[Settings] WHERE [SettingName] like N''NextCreateHistoryFrequencyMinutes'')
		INSERT [dbo].[Settings] ([SettingName], [SettingValue]) 
		VALUES (N''NextCreateHistoryFrequencyMinutes'', N''1440'')
	IF not exists(select * from [dbo].[Settings] WHERE [SettingName] like N''DaysToHistory'')
		INSERT [dbo].[Settings] ([SettingName], [SettingValue]) 
		VALUES (N''DaysToHistory'', N''7'')
	Exec SetCreateHistorySettings @RunCreateHistoryEnabled, @NextCreateHistoryTime, @NextCreateHistoryFrequencyMinutes, @DaysToHistory
		
	IF not exists(select * from [dbo].[Settings] WHERE [SettingName] like N''RepeatTimeSec'')
		INSERT [dbo].[Settings] ([SettingName], [SettingValue]) 
		VALUES (N''RepeatTimeSec'', N''2'')
	Exec SetRepeatTimeSec @RepeatTimeSec
' 
END
GO
/****** Object:  StoredProcedure [dbo].[UpdateEventLogHistory]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateEventLogHistory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[UpdateEventLogHistory]
(
	@fromDate datetime,
	@toDate datetime
)
as

 UPDATE EventLogHistory
 SET Records = (select 
			Records
		from 
			EventLogByHour e
		where 
			h.MachineLogID = e.MachineLogID 
			AND h.EventID = e.EventID
			AND	h.SourceID = e.SourceID
			AND	h.EventTypeName	= e.EventTypeName
			AND h.Date = e.Date
	)
 FROM EventLogHistory h
 WHERE
		h.Date between @fromDate and @toDate
		and Records < (select 
						Records
					from 
						EventLogByHour e with (readpast)
					where 
						h.MachineLogID = e.MachineLogID 
						AND h.EventID = e.EventID
						AND	h.SourceID = e.SourceID
						AND	h.EventTypeName	= e.EventTypeName
						AND h.Date = e.Date
				)' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectEventLogEntriesByMachineLogName]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectEventLogEntriesByMachineLogName]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[SelectEventLogEntriesByMachineLogName]
(
	@FromDate datetime, 
	@ToDate datetime,
	@ComputerName varchar(255),
	@EventLog varchar(255)
)
as
  set deadlock_priority low
  SELECT
		[MachineLogID], 
		[ComputerName],
		[EventLog],
		[RecordNumber],
		[EventID],
		[TimeGenerated],
		[SourceName],
		[EventTypeName],
		[Message],
		[Category],
		[UserName]
  FROM
		EventLog  with (readpast) 
  WHERE
		TimeGenerated between  @FromDate and @ToDate
		and ComputerName = @ComputerName
		and EventLog = @EventLog
  ORDER BY [TimeGenerated] desc
' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectEventLogEntries2]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectEventLogEntries2]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[SelectEventLogEntries2]
(
	@RowCount int,
	@FromDate datetime, @ToDate datetime,
	@EventTypeName varchar(8000) = null,
	@ViewDisabledMachineLogs bit = 0,
	@MachineLogFilter varchar(8000) = null,	
	@EventIdFilter varchar(8000) = null,
	@SourceFilters varchar(8000) = null,
	@MessageFilter nvarchar(3072) = null	
)
as
  set deadlock_priority low
  set rowcount @RowCount
  SELECT
		[MachineLogID], 
		[ComputerName],
		[EventLog],
		[RecordNumber],
		[EventID],
		[TimeGenerated],
		[SourceName],
		[EventTypeName],
		[Message],
		[Category],
		[UserName],
		[Repeat]
  FROM
		EventLog  with (readpast) 
  WHERE
		TimeGenerated between  @FromDate and @ToDate
		and
			(
				(@EventTypeName is null) or 
				(EventTypeName in (select * from ufn_CSVToTable(@EventTypeName)))
			)
		and 
		  (
			((not @MachineLogFilter is null) and MachineLogID in 
			   (
					select MachineLogID 
					from dbo.MachineLogs
					where (Machine + ''\'' + LogName ) in 
						(
							select * from ufn_CSVToTable(@MachineLogFilter)
						)
			   ) 
			) or 
			((@MachineLogFilter is null) and (@ViewDisabledMachineLogs = 0) and (EnabledMachine = 1) ) or 
			((@MachineLogFilter is null) and (@ViewDisabledMachineLogs = 1))
		  )
		and
		  (
			((@EventIdFilter is null)) or --(@ViewAllEventID = 1) or 
			(EventID in (select * from ufn_CSVToTableInt(@EventIdFilter)))
		  )
		and
		  (
			((@SourceFilters is null)) or --(@ViewAllSources = 1) or 
			(SourceName in (select * from ufn_CSVToTable(@SourceFilters)))
		  )
		and 
		  (
			(@MessageFilter is null) or
			([Message] like @MessageFilter) --''%'' +
		  )
  ORDER BY [TimeGenerated] desc
  
  set rowcount 0
' 
END
GO
/****** Object:  StoredProcedure [dbo].[SelectEventLogEntries]    Script Date: 07/08/2010 14:34:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectEventLogEntries]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[SelectEventLogEntries]
(
	@RowCount int,
	@FromDate datetime, @ToDate datetime,
	@EventTypeName varchar(8000) = null,
	@ViewDisabledMachineLogs bit = 0,
	--@ViewAllMachineLogs bit = 1,
	@MachineLogFilter varchar(8000) = null,	
	--@ViewAllEventID bit = 1,
	@EventIdFilter varchar(8000) = null,
	--@ViewAllSources bit = 1,
	@SourceFilters varchar(8000) = null,
	@MessageFilter nvarchar(3072) = null	
)
as
  set deadlock_priority low
  set rowcount @RowCount
  SELECT
		[MachineLogID], 
		[ComputerName],
		[EventLog],
		[RecordNumber],
		[EventID],
		[TimeGenerated],
		[SourceName],
		[EventTypeName],
		[Message],
		[Category],
		[UserName],
		[Repeat]
  FROM
		EventLog  with (readpast) 
  WHERE
		TimeGenerated between  @FromDate and @ToDate
		and
			(
				(@EventTypeName is null) or 
				(EventTypeName in (select * from ufn_CSVToTable(@EventTypeName)))
			)
		and 
		  (
			((not @MachineLogFilter is null) and MachineLogID in (select * from  ufn_CSVToTableInt(@MachineLogFilter)) ) or --(@ViewAllMachineLogs = 0)
			((@MachineLogFilter is null) and (@ViewDisabledMachineLogs = 0) and (EnabledMachine = 1) ) or --(@ViewAllMachineLogs = 1)
			((@MachineLogFilter is null) and (@ViewDisabledMachineLogs = 1)) --(@ViewAllMachineLogs = 1)
		  )
		and
		  (
			((@EventIdFilter is null)) or --(@ViewAllEventID = 1) or 
			(EventID in (select * from ufn_CSVToTableInt(@EventIdFilter)))
		  )
		and
		  (
			((@SourceFilters is null)) or --(@ViewAllSources = 1) or 
			(SourceName in (select * from ufn_CSVToTable(@SourceFilters)))
		  )
		and 
		  (
			(@MessageFilter is null) or
			([Message] like @MessageFilter) --''%'' +
		  )
  ORDER BY [TimeGenerated] desc
  
  set rowcount 0
' 
END
GO
/****** Object:  StoredProcedure [dbo].[DoReArchiveEntries4Day]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DoReArchiveEntries4Day]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[DoReArchiveEntries4Day]
(
	@Year int,
	@Month int,
	@Day int
)
as
  DELETE EventLogHistory 
  WHERE [Year] = @Year and
	   [Month] = @Month and
		[Day] = @Day
		
  UPDATE dbo.EventLogEntries
  SET Archived = 0
  WHERE 
	year(TimeGenerated) = @Year and
	month(TimeGenerated) = @Month and
	day(TimeGenerated) = @Day

  DECLARE @ReArchiveDate datetime
  DECLARE @ReArchiveDateTo datetime
  set @ReArchiveDate = CONVERT(datetime, cast(@Year as CHAR(4)) + ''/'' + CAST(@Month as CHAR(2)) + ''/'' + CAST(@Day as CHAR(2)))
  set @ReArchiveDateTo = dateadd(DAY, 1, @ReArchiveDate)
  exec dbo.DoArchiveEntriesByDates @ReArchiveDate, @ReArchiveDateTo
' 
END
GO
/****** Object:  StoredProcedure [dbo].[DoMaintenance]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DoMaintenance]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE proc [dbo].[DoMaintenance]
as
	exec DoMaintenanceMachineLog
	exec DoMaintenanceGlobal
' 
END
GO
/****** Object:  StoredProcedure [dbo].[DoCreateHistory]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DoCreateHistory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[DoCreateHistory]
as
  DECLARE @fromDate datetime
  DECLARE @toDate datetime
  DECLARE @DaysToHistory int
  
  if exists(select * from Settings 
			Where SettingValue = ''True'' and
				SettingName = ''RunCreateHistoryEnabled'')
  begin
	  Select @DaysToHistory = CAST(SettingValue as int)
	  From Settings
	  Where SettingName like ''DaysToHistory''
	  
	  Set @toDate = CONVERT (date, GETDATE())
	  set @fromDate = DATEADD(DAY, -@DaysToHistory, @toDate)

	  exec DoArchiveEntriesByDates @fromDate, @toDate
  end' 
END
GO
/****** Object:  StoredProcedure [dbo].[CreateEventLogHistory]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CreateEventLogHistory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[CreateEventLogHistory]
(
  @ToDate datetime = null ,
  @Days int = 1
)
as
 declare @FromDate datetime
 if (@ToDate is null)
   SET @ToDate = convert(datetime, convert(char(10),getdate(),111))
 else
   SET @ToDate = convert(datetime, convert(char(10),@ToDate,111))
 SET @FromDate = @ToDate - @Days
 INSERT EventSources (Source)
 SELECT SourceName
 FROM EventLog e
 WHERE TimeGenerated between @FromDate and @ToDate AND
	not exists(SELECT SourceID FROM EventSources s WHERE 
		e.SourceName = s.Source)
 GROUP BY SourceName
 
 INSERT EventLogHistory(
	MachineLogID, 
	EventID, 
	[Year], 
	[Month], 
	[Day], 
	[Hour], 
	SourceID,
	EventTypeName, 
	Records)
 SELECT 
	m.MachineLogID, 
	e.EventID,
	Year(e.TimeGenerated) as [Year],
	Month(e.TimeGenerated) as [Month],
	Day(e.TimeGenerated) as [Day],
	datepart(hh, e.TimeGenerated) as [Hour],
	s.SourceID,
	Left(e.EventTypeName, 1),
	Count(e.RecordNumber) as Records
 FROM
	(EventLog e INNER JOIN MachineLogs m 
		on e.ComputerName = m.Machine AND e.EventLog = m.LogName)
	INNER JOIN EventSources s 
		on e.SourceName = s.Source
 WHERE 
	TimeGenerated between @FromDate and @ToDate and
	not exists(select * from EventLogHistory h 
		where 	h.MachineLogID = m.MachineLogID AND
			e.EventID = h.EventID AND
			Year(e.TimeGenerated) = h.[Year] AND
			Month(e.TimeGenerated) = h.[Month] AND
			Day(e.TimeGenerated) = h.[Day] AND
			datepart(hh, e.TimeGenerated) = h.[Hour] AND
			s.SourceID = h.SourceID AND
			left(e.EventTypeName,1) = h.EventTypeName
			)
 GROUP BY 
	m.MachineLogID, 
	e.EventID,
	Year(e.TimeGenerated),
	Month(e.TimeGenerated),
	Day(e.TimeGenerated),
	datepart(hh, e.TimeGenerated),
	s.SourceID,
	Left(e.EventTypeName, 1)

' 
END
GO
/****** Object:  StoredProcedure [dbo].[InsertEventLogHistory]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertEventLogHistory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[InsertEventLogHistory]
(
	@fromDate datetime,
	@toDate datetime
)
as
 --First insert any new entries
 INSERT EventLogHistory(
	MachineLogID, 
	EventID, 
	[Year], 
	[Month], 
	[Day], 
	[Hour], 
	SourceID,
	EventTypeName, 
	Records)
 SELECT
	e.MachineLogID, 
	e.EventID,
	[Year],
	[Month],
	[Day],
	[Hour],
	e.SourceID,
	e.EventTypeName,
	Records
 FROM 
	EventLogByHour e with (readpast)
 WHERE 
	Date between @fromDate and @toDate AND
	not exists(select * from EventLogHistory h 
		where 	e.MachineLogID = h.MachineLogID AND
			e.EventID = h.EventID AND
			e.[Year] = h.[Year] AND
			e.[Month] = h.[Month] AND
			e.[Day] = h.[Day] AND
			e.[Hour] = h.[Hour] AND
			e.SourceID = h.SourceID AND
			e.EventTypeName = h.EventTypeName
			)' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetStatsSourceEventIDHourlyTotalLive]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStatsSourceEventIDHourlyTotalLive]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create proc [dbo].[GetStatsSourceEventIDHourlyTotalLive]
(
  @date datetime
)
AS
  SELECT 
	(ComputerName + ''\'' + EventLog) as [Log],
	SourceName as [Source],
	EventID,
	sum(case DatePart(hh, TimeGenerated)
		when 0 then 1 else 0 end ) as [0],
	sum(case DatePart(hh, TimeGenerated)
		when 1 then 1 else 0 end ) as [1],
	sum(case DatePart(hh, TimeGenerated)
		when 2 then 1 else 0 end ) as [2],
	sum(case DatePart(hh, TimeGenerated)
		when 3 then 1 else 0 end ) as [3],
	sum(case DatePart(hh, TimeGenerated)
		when 4 then 1 else 0 end ) as [4],
	sum(case DatePart(hh, TimeGenerated)
		when 5 then 1 else 0 end ) as [5],
	sum(case DatePart(hh, TimeGenerated)
		when 6 then 1 else 0 end ) as [6],
	sum(case DatePart(hh, TimeGenerated)
		when 7 then 1 else 0 end ) as [7],
	sum(case DatePart(hh, TimeGenerated)
		when 8 then 1 else 0 end ) as [8],
	sum(case DatePart(hh, TimeGenerated)
		when 9 then 1 else 0 end ) as [9],
	sum(case DatePart(hh, TimeGenerated)
		when 10 then 1 else 0 end ) as [10],
	sum(case DatePart(hh, TimeGenerated)
		when 11 then 1 else 0 end ) as [11],
	sum(case DatePart(hh, TimeGenerated)
		when 12 then 1 else 0 end ) as [12],
	sum(case DatePart(hh, TimeGenerated)
		when 13 then 1 else 0 end ) as [13],
	sum(case DatePart(hh, TimeGenerated)
		when 14 then 1 else 0 end ) as [14],
	sum(case DatePart(hh, TimeGenerated)
		when 15 then 1 else 0 end ) as [15],
	sum(case DatePart(hh, TimeGenerated)
		when 16 then 1 else 0 end ) as [16],
	sum(case DatePart(hh, TimeGenerated)
		when 17 then 1 else 0 end ) as [17],
	sum(case DatePart(hh, TimeGenerated)
		when 18 then 1 else 0 end ) as [18],
	sum(case DatePart(hh, TimeGenerated)
		when 19 then 1 else 0 end ) as [19],
	sum(case DatePart(hh, TimeGenerated)
		when 20 then 1 else 0 end ) as [20],
	sum(case DatePart(hh, TimeGenerated)
		when 21 then 1 else 0 end ) as [21],
	sum(case DatePart(hh, TimeGenerated)
		when 22 then 1 else 0 end ) as [22],
	sum(case DatePart(hh, TimeGenerated)
		when 21 then 1 else 0 end ) as [23],
	Count(*) as [Total]
  FROM 
	dbo.EventLog
  WHERE 
	TimeGenerated between @date and dateadd(dd, 1, @date)
  GROUP BY
	ComputerName + ''\'' + EventLog,
	SourceName,
	EventID
  ORDER BY
	ComputerName + ''\'' + EventLog,
	SourceName,
	EventID

' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetTimeBreakdownByMessage]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetTimeBreakdownByMessage]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[GetTimeBreakdownByMessage]
(
	@FromDate datetime,
	@ToDate datetime,
	@Message nvarchar(1000)
)
as
  select 
	ComputerName,
	EventLog,
	SourceName,
	EventID,
	year(TimeGenerated) as [Year],
	month(TimeGenerated) as [Month],
	day(TimeGenerated) as [Day],
	DATEPART(hh, TimeGenerated) as [Hour],
	DATEPART(N, TimeGenerated) as [Minute],
	EventTypeName,
	COUNT(*) as [Count]
  from EventLog readpast 
  where 
	TimeGenerated between @FromDate and @ToDate
	and
	[Message] like @Message + ''%''
group by 
	ComputerName,
	EventLog,
	SourceName,
	EventID,
	year(TimeGenerated),
	month(TimeGenerated),
	day(TimeGenerated),
	DATEPART(hh, TimeGenerated),
	DATEPART(N, TimeGenerated),
	EventTypeName
	
' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetStatsSourceHourlyTotalLive]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStatsSourceHourlyTotalLive]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
create proc [dbo].[GetStatsSourceHourlyTotalLive]
(
  @date datetime
)
AS
  SELECT 
	(ComputerName + ''\'' + EventLog) as [Log],
	SourceName as [Source],
	sum(case DatePart(hh, TimeGenerated)
		when 0 then 1 else 0 end ) as [0],
	sum(case DatePart(hh, TimeGenerated)
		when 1 then 1 else 0 end ) as [1],
	sum(case DatePart(hh, TimeGenerated)
		when 2 then 1 else 0 end ) as [2],
	sum(case DatePart(hh, TimeGenerated)
		when 3 then 1 else 0 end ) as [3],
	sum(case DatePart(hh, TimeGenerated)
		when 4 then 1 else 0 end ) as [4],
	sum(case DatePart(hh, TimeGenerated)
		when 5 then 1 else 0 end ) as [5],
	sum(case DatePart(hh, TimeGenerated)
		when 6 then 1 else 0 end ) as [6],
	sum(case DatePart(hh, TimeGenerated)
		when 7 then 1 else 0 end ) as [7],
	sum(case DatePart(hh, TimeGenerated)
		when 8 then 1 else 0 end ) as [8],
	sum(case DatePart(hh, TimeGenerated)
		when 9 then 1 else 0 end ) as [9],
	sum(case DatePart(hh, TimeGenerated)
		when 10 then 1 else 0 end ) as [10],
	sum(case DatePart(hh, TimeGenerated)
		when 11 then 1 else 0 end ) as [11],
	sum(case DatePart(hh, TimeGenerated)
		when 12 then 1 else 0 end ) as [12],
	sum(case DatePart(hh, TimeGenerated)
		when 13 then 1 else 0 end ) as [13],
	sum(case DatePart(hh, TimeGenerated)
		when 14 then 1 else 0 end ) as [14],
	sum(case DatePart(hh, TimeGenerated)
		when 15 then 1 else 0 end ) as [15],
	sum(case DatePart(hh, TimeGenerated)
		when 16 then 1 else 0 end ) as [16],
	sum(case DatePart(hh, TimeGenerated)
		when 17 then 1 else 0 end ) as [17],
	sum(case DatePart(hh, TimeGenerated)
		when 18 then 1 else 0 end ) as [18],
	sum(case DatePart(hh, TimeGenerated)
		when 19 then 1 else 0 end ) as [19],
	sum(case DatePart(hh, TimeGenerated)
		when 20 then 1 else 0 end ) as [20],
	sum(case DatePart(hh, TimeGenerated)
		when 21 then 1 else 0 end ) as [21],
	sum(case DatePart(hh, TimeGenerated)
		when 22 then 1 else 0 end ) as [22],
	sum(case DatePart(hh, TimeGenerated)
		when 21 then 1 else 0 end ) as [23],
	Count(*) as [Total]
  FROM 
	dbo.EventLog
  WHERE 
	TimeGenerated between @date and dateadd(dd, 1, @date)
  GROUP BY
	ComputerName + ''\'' + EventLog,
	SourceName
  ORDER BY
	ComputerName + ''\'' + EventLog,
	SourceName

' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetMachineLogMaxTimeGenerated]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetMachineLogMaxTimeGenerated]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[GetMachineLogMaxTimeGenerated]
(
	@MachineLogID int
)
as
  SELECT 
		max(TimeGenerated) as MaxTimeGenerated
  FROM 
		EventLog 
  WHERE
		MachineLogID = @MachineLogID
' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetStatsMachineHourlyTotalLive]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStatsMachineHourlyTotalLive]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE proc [dbo].[GetStatsMachineHourlyTotalLive]
(
  @date datetime
)
AS
  SELECT 
	ComputerName + ''\'' + EventLog as [Log],
	sum(case DatePart(hh, TimeGenerated)
		when 0 then 1 else 0 end ) as [0],
	sum(case DatePart(hh, TimeGenerated)
		when 1 then 1 else 0 end ) as [1],
	sum(case DatePart(hh, TimeGenerated)
		when 2 then 1 else 0 end ) as [2],
	sum(case DatePart(hh, TimeGenerated)
		when 3 then 1 else 0 end ) as [3],
	sum(case DatePart(hh, TimeGenerated)
		when 4 then 1 else 0 end ) as [4],
	sum(case DatePart(hh, TimeGenerated)
		when 5 then 1 else 0 end ) as [5],
	sum(case DatePart(hh, TimeGenerated)
		when 6 then 1 else 0 end ) as [6],
	sum(case DatePart(hh, TimeGenerated)
		when 7 then 1 else 0 end ) as [7],
	sum(case DatePart(hh, TimeGenerated)
		when 8 then 1 else 0 end ) as [8],
	sum(case DatePart(hh, TimeGenerated)
		when 9 then 1 else 0 end ) as [9],
	sum(case DatePart(hh, TimeGenerated)
		when 10 then 1 else 0 end ) as [10],
	sum(case DatePart(hh, TimeGenerated)
		when 11 then 1 else 0 end ) as [11],
	sum(case DatePart(hh, TimeGenerated)
		when 12 then 1 else 0 end ) as [12],
	sum(case DatePart(hh, TimeGenerated)
		when 13 then 1 else 0 end ) as [13],
	sum(case DatePart(hh, TimeGenerated)
		when 14 then 1 else 0 end ) as [14],
	sum(case DatePart(hh, TimeGenerated)
		when 15 then 1 else 0 end ) as [15],
	sum(case DatePart(hh, TimeGenerated)
		when 16 then 1 else 0 end ) as [16],
	sum(case DatePart(hh, TimeGenerated)
		when 17 then 1 else 0 end ) as [17],
	sum(case DatePart(hh, TimeGenerated)
		when 18 then 1 else 0 end ) as [18],
	sum(case DatePart(hh, TimeGenerated)
		when 19 then 1 else 0 end ) as [19],
	sum(case DatePart(hh, TimeGenerated)
		when 20 then 1 else 0 end ) as [20],
	sum(case DatePart(hh, TimeGenerated)
		when 21 then 1 else 0 end ) as [21],
	sum(case DatePart(hh, TimeGenerated)
		when 22 then 1 else 0 end ) as [22],
	sum(case DatePart(hh, TimeGenerated)
		when 21 then 1 else 0 end ) as [23],
	Count(*) as [Total]
  FROM 
	dbo.EventLog
  WHERE 
	TimeGenerated between @date and dateadd(dd, 1, @date)
  GROUP BY
	ComputerName,
	EventLog
  ORDER BY
	ComputerName,
	EventLog

' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetStatsMachineHourlyTotalEWLive]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStatsMachineHourlyTotalEWLive]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE proc [dbo].[GetStatsMachineHourlyTotalEWLive]
(
  @date datetime
)
AS
  SELECT 
	ComputerName + ''\'' + EventLog as [Log],
	EventTypeName as [Type],
	sum(case DatePart(hh, TimeGenerated)
		when 0 then 1 else 0 end ) as [0],
	sum(case DatePart(hh, TimeGenerated)
		when 1 then 1 else 0 end ) as [1],
	sum(case DatePart(hh, TimeGenerated)
		when 2 then 1 else 0 end ) as [2],
	sum(case DatePart(hh, TimeGenerated)
		when 3 then 1 else 0 end ) as [3],
	sum(case DatePart(hh, TimeGenerated)
		when 4 then 1 else 0 end ) as [4],
	sum(case DatePart(hh, TimeGenerated)
		when 5 then 1 else 0 end ) as [5],
	sum(case DatePart(hh, TimeGenerated)
		when 6 then 1 else 0 end ) as [6],
	sum(case DatePart(hh, TimeGenerated)
		when 7 then 1 else 0 end ) as [7],
	sum(case DatePart(hh, TimeGenerated)
		when 8 then 1 else 0 end ) as [8],
	sum(case DatePart(hh, TimeGenerated)
		when 9 then 1 else 0 end ) as [9],
	sum(case DatePart(hh, TimeGenerated)
		when 10 then 1 else 0 end ) as [10],
	sum(case DatePart(hh, TimeGenerated)
		when 11 then 1 else 0 end ) as [11],
	sum(case DatePart(hh, TimeGenerated)
		when 12 then 1 else 0 end ) as [12],
	sum(case DatePart(hh, TimeGenerated)
		when 13 then 1 else 0 end ) as [13],
	sum(case DatePart(hh, TimeGenerated)
		when 14 then 1 else 0 end ) as [14],
	sum(case DatePart(hh, TimeGenerated)
		when 15 then 1 else 0 end ) as [15],
	sum(case DatePart(hh, TimeGenerated)
		when 16 then 1 else 0 end ) as [16],
	sum(case DatePart(hh, TimeGenerated)
		when 17 then 1 else 0 end ) as [17],
	sum(case DatePart(hh, TimeGenerated)
		when 18 then 1 else 0 end ) as [18],
	sum(case DatePart(hh, TimeGenerated)
		when 19 then 1 else 0 end ) as [19],
	sum(case DatePart(hh, TimeGenerated)
		when 20 then 1 else 0 end ) as [20],
	sum(case DatePart(hh, TimeGenerated)
		when 21 then 1 else 0 end ) as [21],
	sum(case DatePart(hh, TimeGenerated)
		when 22 then 1 else 0 end ) as [22],
	sum(case DatePart(hh, TimeGenerated)
		when 21 then 1 else 0 end ) as [23],
	Count(*) as [Total]
  FROM 
	dbo.EventLog
  WHERE 
	TimeGenerated between @date and dateadd(dd, 1, @date)
  GROUP BY
	ComputerName,
	EventLog,
	EventTypeName
  ORDER BY
	ComputerName,
	EventLog,
	EventTypeName

' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetStatsDailyErrWarnPerMinute]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStatsDailyErrWarnPerMinute]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[GetStatsDailyErrWarnPerMinute]
(
	@date datetime
)
as 
  
  SELECT 
	cast(Year(TimeGenerated) as char(4)) + ''-'' +
	cast(Month(TimeGenerated) as varchar(2)) + ''-'' + 
	cast(Day(TimeGenerated) as varchar(2)) + '' '' +
	right(''0'' + cast(DatePart(hh, TimeGenerated) as varchar(2)), 2) + '':'' + 
	right(''0'' + cast(DatePart(mi, TimeGenerated) as varchar(2)), 2) as [Period],
	sum(case EventTypeName
		when  ''Error'' then 1 else 0 end) as [Errors],
	sum(case EventTypeName
		when  ''Warning'' then 1 else 0 end) as [Warnings],
	Count(*) as [Total]
  FROM 
	EventLog
  WHERE 
	TimeGenerated between @date and dateadd(dd, 1, @date)
  GROUP BY 
	cast(Year(TimeGenerated) as char(4)) + ''-'' +
	cast(Month(TimeGenerated) as varchar(2)) + ''-'' + 
	cast(Day(TimeGenerated) as varchar(2)) + '' '' +
	right(''0'' + cast(DatePart(hh, TimeGenerated) as varchar(2)), 2) + '':'' + 
	right(''0'' + cast(DatePart(mi, TimeGenerated) as varchar(2)), 2)
  ORDER BY
	cast(Year(TimeGenerated) as char(4)) + ''-'' +
	cast(Month(TimeGenerated) as varchar(2)) + ''-'' + 
	cast(Day(TimeGenerated) as varchar(2)) + '' '' +
	right(''0'' + cast(DatePart(hh, TimeGenerated) as varchar(2)), 2) + '':'' + 
	right(''0'' + cast(DatePart(mi, TimeGenerated) as varchar(2)), 2)

' 
END
GO
/****** Object:  StoredProcedure [dbo].[GetMachineLogTotalStats]    Script Date: 07/08/2010 14:34:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetMachineLogTotalStats]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[GetMachineLogTotalStats]
as
SELECT 
	Machine,
	LogName,
	sum(Records) as Records
FROM 
	dbo.MachineLogs m inner join dbo.EventLogHourlyCounts c 
		on m.MachineLogID = c.MachineLogID
GROUP BY 
	Machine,
	LogName
ORDER BY
	Machine,
	LogName' 
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectMachineLogListAll]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE proc dbo.SelectMachineLogListAll
as
  SELECT 
		m.MachineLogID,
		c.CollectorID,
		c.Name as [Collector],
		[Machine],
		[LogName],
		[LastPollTime],
		[Enabled],
		[PollFrequency],
		[MaxEntryCount],
		[PollFilterID]
  FROM 
	(MachineLogs m inner join Collectors c 
	  on m.CollectorID = c.CollectorID) 
  Order by 
		[Machine],
		[LogName]' 
END
GO